Power Query Running Count Based on Row Condition – Excel Magic Trick 1588

Summary

このビデオでは、Excelのワークシートで行ったように、Power Queryでランニングカウントを実装する方法が説明されています。Excelではセル参照を使用して簡単に行うことができるが、Power Queryではテーブル参照とカスタム関数を用いて行う必要があります。ビデオでは、データがExcelのテーブルにある場合、まずデータを並べ替え、テーブルをバッファリングし、カスタム列を追加してカウントを実装する方法をステップバイステップで説明しています。最後に、データ型を整数に変更して、Power Queryの計算結果をExcelシートにロードする方法も紹介されています。

Takeaways

• 📊 エクセルのワークシートでは、特定のアイテムの逐次カウントを簡単に行うことができますが、Power Queryでは異なるアプローチが必要です。
• 🔢 逐次カウントを実行するには、列内のアイテムが一定の順序で並んでいる必要があります。
• 💾 Power Queryを使用するには、データがExcelのテーブル内にあらかじめ存在している必要があります。
• 🔧 Power Queryでは、セル参照を使用して列全体またはその一部、または個々の行を参照することはできません。
• 📈 Power Queryで逐次カウントを実行するには、テーブルをソートし、その順序でテーブルを選択する必要があります。
• 🔗 Power Queryでテーブルを参照する際には、テーブルをバッファリングして、各行で参照する必要があります。
• 🛠️ Mコードを使用してカスタム列を追加し、逐次カウントのロジックを構築します。
• 🔍 内部テーブルから外部テーブルの行と比較する条件を設定する必要があります。
• 📋 Power Queryでは、テーブルから行を数えるためには、table.rowcount()関数を使用します。
• 🎓 新しいユーザーは、MSP TDAクラスのYouTubeビデオを通じてPower QueryとMコードの基礎を学ぶことができます。

Q & A

Excelのエクセルで行うランニングカウントはどのようにPower Queryで実現できますか？

Power Queryでは、Excelのセル参照のように列全体または個々の行を参照する機能がないため、テーブルの選択とフィルタリングを使用してランニングカウントを計算します。

Power Queryでランニングカウントを計算する前に何が必要ですか？

ランニングカウントを計算する前に、列が一定の順序で並んでいる必要があります。また、データがExcelのテーブルに既に存在している必要があります。

Power Queryでテーブルをソートするにはどうすればいいですか？

Power Queryでテーブルをソートするには、列のドロップダウンから「昇順」「降順」のいずれかを選択します。

「バッファリング」とはPower Queryで何を意味しますか？

「バッファリング」とは、テーブルを1回だけ読み込んで、各行の計算でその読み込まれたテーブルを使用するプロセスです。

Power Queryでカスタム列を追加する際に「each」キーワードは何を意味しますか？

「each」キーワードは、Mコードのカスタム関数で使用され、各行に対して関数を実行する際の省略形です。

Power Queryで行をフィルタリングする際に使用する関数は何ですか？

行をフィルタリングする際には、`Table.SelectRows` 関数を使用します。

Power Queryで行の数を数えるにはどのような関数を使用しますか？

行の数を数えるには、`Table.RowCount` 関数を使用します。

Power Queryでデータ型を変更するにはどうすればいいですか？

データ型を変更するには、列を右クリックして「データの変更」を選択し、希望のデータ型に変更します。

Power Queryで計算結果をExcelの既存のシートにロードするにはどうすればいいですか？

計算結果をExcelの既存のシートにロードするには、「閉じる＆ロード」オプションを使用し、必要に応じて既存のシートを選択します。

Power QueryやMコードの基礎を学ぶためにおすすめのリソースは何ですか？

Power QueryやMコードの基礎を学ぶためには、YouTubeのMSP TDAクラスの無料ビデオ（ビデオ3から12）がおすすめです。

Outlines

00:00

📊 ExcelからPower Queryへの移行とランニングカウントの作成

この段落では、Excelのワークシートで使用していたフォーミュラをPower Queryに移行する方法について説明しています。Excelでは、セル参照を使用して列ごとにアイテムのランニングカウントを簡単に作成できたが、Power Queryでは異なるアプローチが必要です。データがExcelのテーブルにある場合、countifs関数を使用するのが簡単ですが、Power PivotやPower BIソリューションを構築したり、複雑なデータをインポートする場合はPower Queryが適しています。ランニングカウントを作成するには、まず列内のアイテムが一定の順序で並んでいる必要があります。ここでは、取引番号や日付、時間などに基づいて並べ替え、Power Queryウィンドウを開いてクエリを作成し、カスタム列とカスタム関数を用いて処理する方法が説明されています。

05:00

🔍 Power Queryでのランニングカウントの詳細処理

この段落では、Power Queryでランニングカウントを作成する具体的な手順について詳しく説明しています。まず、列をソートし、テーブルをバッファリングすることで、各行で参照するテーブルを一度ロードしてから使用するようにしています。カスタム列を追加し、Mコードを使用してテーブルから行と列をフィルタリングし、条件を満たす行の数をカウントする方法が示されています。このプロセスでは、内部テーブルと外部テーブルの関係を理解し、条件を適切に設定することが重要です。最後に、データ型を整数に設定し、Excelの既存のシートにロードする方法についても触れています。このセクションは、Power QueryやMコードの初心者向けに、YouTubeの無料ビデオコースを 홍보しています。

Keywords

💡Power Query

Power Queryは、ExcelやPower BIで使用されるデータ変換およびデータ取得の機能です。ビデオでは、Excelのセル参照を使った簡単な操作とは異なり、Power Queryではデータセット全体を扱い、複雑なデータインポートや分析を行う際に役立ちます。ビデオでは、Power Queryを使用して行うデータのランニングカウントの方法が説明されています。

💡Running Count

ランニングカウントとは、データセット内の特定のアイテムごとに逐次増加する数値を意味します。ビデオでは、ExcelのカウントIF機能とは異なり、Power Query内でランニングカウントを実装する方法が解説されています。これは、データが時間順やトランザクション番号順に整然と並んでいる場合にのみ意味を持ちます。

💡Table.Buffer

Table.BufferはPower QueryのMコードの機能で、テーブルをバッファリングしてメモリにロードし、各行に対して再利用できるようにします。ビデオでは、テーブルをバッファリングすることで、行ごとにテーブル全体を参照する必要性をなくし、パフォーマンスを向上させる方法が示されています。

💡Table.SelectRows

Table.SelectRowsは、特定の条件に基づいてテーブルから行を選択するPower QueryのMコードの関数です。ビデオでは、この関数を使用して、ランニングカウントを計算するために、テーブル内の特定の条件に一致する行をフィルタリングする方法が説明されています。

💡Each

EachはMコードのキーワードで、テーブルの各行に対して関数を適用する方法を定義します。ビデオでは、Eachを使用して、テーブルの各行に対してカスタム関数を実行し、ランニングカウントを計算する例が示されています。

💡M Code

MコードはPower Queryで使用されるプログラミング言語で、データ変換ロジックを記述するために使用されます。ビデオでは、Mコードを使用してPower Queryの機能を活用し、Excelの機能を超えたデータ操作を行う方法が解説されています。

💡Custom Column

カスタム列とは、Power Queryでテーブルに新しい列を追加し、その列に計算結果やデータ変換の出力を格納する方法です。ビデオでは、カスタム列を通じてランニングカウントの計算結果をテーブルに追加する方法が説明されています。

💡Row Count

行数のカウントは、テーブル内の行の数を数えるプロセスです。ビデオでは、Table.RowCount関数を使用して、特定の条件に一致する行の数をカウントし、ランニングカウントを計算する方法が示されています。

💡Data Type

データタイプは、データの性質や形式を定義するものです。ビデオでは、計算結果のデータタイプを整数に設定することで、正確なランニングカウントを確保する方法が説明されています。これは、データの整合性と正確性を確保するために重要です。

💡Power Pivot

Power Pivotは、ExcelやPower BIで使用されるデータモデリング機能です。ビデオでは、Power QueryとPower Pivotを組み合わせて複雑なデータセットを操作し、分析情報を提供する例が示されています。

Highlights

在Excel工作表中复制公式以实现每行特定项目的运行计数是容易的，但在Power Query中如何实现呢？

如果数据已经在Excel工作表中，使用COUNTIFS比使用Power Query更容易。

构建Power Pivot或Power BI解决方案，或者导入复杂数据时，Power Query是正确的工具。

运行计数需要列中的项目有一定的顺序。

数据集已经在Excel表中，通过“从表获取和转换”打开Power Query窗口。

创建自定义列并构建自定义Power Query函数。

Power Query中没有允许查看整个列或部分列或单个行的单元格引用。

在Excel中，我们可以使用单元格引用创建可扩展范围，但在Power Query中需要使用Table.SelectRows。

首先需要对列进行排序，以便在刷新时保持正确的顺序。

使用Table.Buffer将整个表加载一次，然后每行都使用该加载的表。

在自定义列中，使用Table.AddColumn函数填充列。

使用Table.SelectRows函数过滤表，需要从内部表获取列并与外部表进行比较。

定义变量并使用Table.SelectRows函数进行过滤，确保每行都能看到实际的IT数字。

使用Table.SelectRows和逻辑测试运算符进一步过滤，以匹配特定条件。

使用Table.RowCount计算行数，创建可扩展范围的运行计数列。

最后，将数据类型设置为整数。

加载数据到现有工作表，虽然比Excel中的单元格引用和COUNTIFS更复杂，但有时需要使用M代码在Power Query中进行相同的计算。

对于Power Query或M代码的新手，可以查看YouTube上的MSTDA课程视频，学习Power Query和M代码的所有知识。

Transcripts

a formula like this in an Excel

worksheet when we copy it down so that

we can have a running count for

particular items for each row easy to do

in an Excel worksheet but how do we do

it in power query now if your data is

already in an excel worksheet it's much

easier to use countifs than it is to use

power query but if you're building a

power pivot power bi solution or you're

importing a lot of complicated data then

power queries the right tool so let's

see how to do this now the first thing

about a running count is there has to be

some order to the items in the column

for it to make any sense now we have a

transaction number but it could be a

date or a time or something like that

now this data set is already in an Excel

table so we'll go up to data get and

transform from table that opens up the

power query window over here we want to

name our query something like product

running count and enter we have two

steps source and change and we're gonna

create a custom column and we're gonna

build some custom power query functions

now here's the thing about power query

we don't have cell references that

allows us to look at the whole column or

part of the column or an individual row

if we think about how we did it in Excel

we can just use cell references that's

an expandable range that as I copy down

gets bigger and bigger well in power

query to create an expandable range

we're gonna have to use table dot select

rows that means for every single row

where we make a calculation we're gonna

need the full table and actually filter

it then when we try and go get a

conditioner criteria from the row we're

gonna have to do something different

than look at the inside table we're

gonna have to look back to the outside

table now these concepts are foreign to

us excel people because it's so easy to

simply use certain types of cell

references step one is we have to

sort this column that way when we

refresh whatever the sort is in the

Excel worksheet it'll have the correct

sort here so I click the drop-down sort

descending I can see my new step the

next thing is this is the outside table

but we're gonna have to repeat this

entire table for every row now if we

just put the name of the table and

copied it down every row would have to

look back to the outside back to the

outside so we want to add a step and

buffer the table and all that means is

it will load it once and then use that

loaded table for every row now I'm going

to use the f of X button to add a new

step down here there's the name of the

previous step which of course is a full

table table dot and I can see from my

drop-down table dot buffer open

parentheses close parentheses and enter

click over and applied steps f2 and

we're gonna rename this buffer table and

enter now we go to add column custom

column we'll name the new column and

just to remind you how custom columns

work we're just going to fill the column

with ones click OK we can see in the

formula bar that when we use custom

column it uses the power query function

table dot add column now buffer table

here just means it's looking to the full

table from the previous step that's the

name of this new column the word each is

a special word in M code that is

shorthand for a custom function now

because we're going to use two custom

functions back-to-back we want to

explicitly define the functions rather

than use each so here's how we do it

we're going to define our variable in

parenthesis boaty then we're gonna use

the go-to operator equal sign greater

than all of that is explicitly doing

what each did if I hit enter right now

it just says hey for that function

please go to each row and put a 1 now

that's not what we want because we want

first to filter a table so we type table

dot select rows tab the first argument

in this table dot select rows we're

gonna put a table buffer table

, the condition well this is where we

need to get a column from the inside

buffer table remember that table will be

a full table in each row we're gonna

have to get a column from this table and

then compare it to whatever's in the row

from the outside table so we're going to

define our second function this one will

be in side table and the go-to operator

now we need a full column from this

table and then the condition from the

outside table so we say hey inside table

open square brackets and we better spell

this transaction number correctly

let's click expand and now we're going

to run our condition with our

comparative operators how many of you in

the full column are less than or equal

to outside table and then we repeat the

same column name so now for every row in

the table because we have ot it will

always see the actual number for I T

it'll always have the full column now

watch what happens when we hit enter if

we click down in each row well there's

the quad

there's the carlota it is that

expandable range now that's not quite

what we want we want for example right

here we only want quad quad so we come

up to table dot select rows after our

first condition we use the and logical

test operator all lowercase and now we

say inside table product column how many

of you are equal to outside table

product and now when I hit enter that is

looking good carlota and there's our two

quads all the way down at the bottom

there should be just four records with

quad now we need to just count how many

rows there are

before table dot select rows table dot

and I can't remember if it's count row

or row count and look at that I type

table dot count and this intellisense is

kind of working so it's row count very

carefully open parenthesis and then at

the end close parenthesis and enter and

there's that expandable range coughs

running count column now the last step

is we need to add a data type whole

number and now we can home close and

load closing o2 on the existing sheet

maybe a ch3 click OK a lot more

difficult to do than our beautiful cell

references and countifs but sometimes

you need to do this very same

calculation using M code in power query

now if you're new to power query or M

code check out my MSP TDA class free @

youtube videos number 3 to 12 teach you

everything you need to know about power

query and M code

[Music]