Power Query Running Count Based on Row Condition – Excel Magic Trick 1588
Summary
TLDRこのビデオでは、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
📊 ExcelからPower Queryへの移行とランニングカウントの作成
この段落では、Excelのワークシートで使用していたフォーミュラをPower Queryに移行する方法について説明しています。Excelでは、セル参照を使用して列ごとにアイテムのランニングカウントを簡単に作成できたが、Power Queryでは異なるアプローチが必要です。データがExcelのテーブルにある場合、countifs関数を使用するのが簡単ですが、Power PivotやPower BIソリューションを構築したり、複雑なデータをインポートする場合はPower Queryが適しています。ランニングカウントを作成するには、まず列内のアイテムが一定の順序で並んでいる必要があります。ここでは、取引番号や日付、時間などに基づいて並べ替え、Power Queryウィンドウを開いてクエリを作成し、カスタム列とカスタム関数を用いて処理する方法が説明されています。
🔍 Power Queryでのランニングカウントの詳細処理
この段落では、Power Queryでランニングカウントを作成する具体的な手順について詳しく説明しています。まず、列をソートし、テーブルをバッファリングすることで、各行で参照するテーブルを一度ロードしてから使用するようにしています。カスタム列を追加し、Mコードを使用してテーブルから行と列をフィルタリングし、条件を満たす行の数をカウントする方法が示されています。このプロセスでは、内部テーブルと外部テーブルの関係を理解し、条件を適切に設定することが重要です。最後に、データ型を整数に設定し、Excelの既存のシートにロードする方法についても触れています。このセクションは、Power QueryやMコードの初心者向けに、YouTubeの無料ビデオコースを 홍보しています。
Mindmap
Keywords
💡Power Query
💡Running Count
💡Table.Buffer
💡Table.SelectRows
💡Each
💡M Code
💡Custom Column
💡Row Count
💡Data Type
💡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]
5.0 / 5 (0 votes)