each & underscore_ in Power Query Explained
Summary
TLDRこのビデオスクリプトは、パワークエリにおける'each'キーワードとアンダースコアの使い方を詳しく説明しています。'each'はコレクション内の各アイテムを意味し、アンダースコアは文脈によって意味が変わります。テーブルやリストの文脈での実践例を通じて、アンダースコアがテーブルでは行(レコード)を、リストではリストの各アイテムを表すようになる理由が説明されています。また、関数を用いたより複雑なデータ操作の例も紹介されており、パワークエリの構文が簡潔で読みやすいように設計されている点も強調されています。
Takeaways
- 😀 「each」キーワードは、コレクション内の各アイテムを意味しますが、コンテキストによって意味が変わります。
- 🔍 「_」アンダースコアは、その文脈によって意味が変わる特別なキーワードです。テーブルでは行を、リストではリスト内の各アイテムを表します。
- 📊 Power Queryで「Table.AddColumns」関数を使用すると、「each」キーワードとアンダースコアを用いて新しい列を追加できます。
- 💡 アンダースコアを使用すると、テーブルの各行(レコード)を変数として扱うことができます。これにより、行内の特定のフィールド(例えば「amount」)にアクセスできます。
- 📝 「each」とアンダースコアは、関数を簡単に記述するための糖衣構文であり、Power Queryの構文を読みやすくします。
- 🔗 リストの文脈で「each」キーワードとアンダースコアを使用すると、リスト内の各アイテムに対して操作を実行できます。
- 🛠 「each」キーワードとアンダースコアは、関数を定義する際に使用されることができ、これによりテーブルやリストの各アイテムを操作できます。
- 📚 Power Queryの学習者は、このビデオの説明を通じて、「each」キーワードとアンダースコアの使い方をより深く理解できます。
- 🎓 ビデオでは、DAXやPower Queryのコースへのリンクが提供されており、これらは初心者から上級者まで幅広くデータ分析のスキルを身につけるのに役立ちます。
- 🎉 ビデオの最後に、視聴者からのフィードバックや質問を歓迎するメッセージが含まれており、視聴者がより深く学び込む機会を提供しています。
Q & A
「each」キーワードは何を意味するのですか?
-「each」キーワードは、コレクション内の各アイテムを意味します。
アンダースコア(_)は何を意味するのですか?
-アンダースコアは文脈によって意味が変わります。テーブルの文脈では行(レコード)を指し、リストの文脈ではリストの各アイテムを指します。
「each」キーワードとアンダースコアはどのように使用されますか?
-「each」キーワードとアンダースコアは、Power Queryの関数内で使用され、特定のコレクションの各アイテムに対して操作を実行するために使われます。
テーブルに新しい列を追加するにはどうすればいいですか?
-テーブルに新しい列を追加するには、「table.addcolumns」関数を使用し、「each」キーワードとアンダースコアを組み合わせて、各行の特定のフィールドを取得または操作します。
リストに対して「each」キーワードとアンダースコアを使用すると何が起こりますか?
-リストに対して「each」キーワードとアンダースコアを使用すると、リストの各アイテムに対して操作を実行することができます。例えば、リストの各アイテムに1を加算するなどです。
アンダースコアはどのようにして変数として機能するのですか?
-アンダースコアは関数内で使用される際に、コレクションの各アイテム(テーブルの行やリストの要素)を参照する変数として機能します。
「each」キーワードとアンダースコアが提供する「糖衣構文」とは何ですか?
-「糖衣構文」とは、Power Queryの関数をより読みやすく、簡単に記述するための簡略表現です。「each」キーワードとアンダースコアは、関数を定義する際に使用され、その結果を簡単に取得することができます。
関数内で変数を宣言する必要がある理由は何ですか?
-関数内で変数を宣言することにより、関数の処理中に行ったり来たりするデータや値を参照しやすくなります。これにより、コードの可読性と管理性が向上します。
テーブルとリストの文脈で「each」キーワードとアンダースコアを使用する際の違いは何ですか?
-テーブルの文脈ではアンダースコアは行(レコード)を参照し、リストの文脈ではリストの各アイテムを参照します。これは、データ構造によって変数として保持されるデータの性質が異なるためです。
「each」キーワードとアンダースコアを使用する際に文脈が重要である理由は何ですか?
-文脈が重要であるのは、アンダースコアが参照するデータの性質が文脈によって変わることによるためです。テーブルとリストなど異なるデータ構造に対して同じキーワードを使用する場合、期待する動作が異なるためです。
Outlines
🔍 各キーワードの理解:Power Queryでの「each」と「_」
このセクションでは、Power Queryにおける「each」と「_」キーワードの使い方を解説しています。まず、「each」はコレクション内の各アイテムを指し、「_」は文脈によって意味が変わると説明しました。次に、テーブルに新しい列を追加する例を通じて、「each」と「_」がどのように機能するかを実演しました。テーブルの文脈では、「_」は行(レコード)を指し、リストの文脈ではリスト内の各アイテムを指すことを示しました。また、「each」と「_」は関数の糖衣構文であり、Power Queryのシンタックスを読みやすくする役割を果たすと述べています。
🛠 関数による「each」と「_」の代替表現
このセクションでは、「each」と「_」を関数として代替する例を示しています。まず、テーブルに対して変数を宣言し、その変数がテーブルの各行(レコード)を保持することを説明しました。次に、変数を関数として使用し、レコードから特定のフィールドを抽出する方法を実演しました。同様に、リストに対しても関数を宣言し、リスト内の各アイテムに対して操作を行う方法を説明しました。最後に、関数を用いた「each」と「_」の代替表現の利便性を強調し、より複雑なクエリでもこれらの概念がどのように適用されるかについても触れています。
Mindmap
Keywords
💡Power Query
💡each
💡underscore
💡table.add columns
💡record
💡list.transform
💡function
💡variable
💡context
💡sugar syntax
Highlights
Explains the 'each' keyword in Power Query
Describes the underscore's context-dependent meaning
Shows how to add a column using 'table.addcolumns'
Clarifies what 'each' means in the context of a table
Demonstrates capturing the entire row with the underscore
Illustrates extracting a specific field from a record
Discusses the difference between 'each' and underscore in lists
Explains how underscore captures every single item in a list
Shows how to transform a list using 'list.transform'
Details the importance of context with 'each' and underscore
Provides an alternative syntax to 'each' and underscore
Demonstrates declaring a variable to replace 'each' and underscore
Shows how to create a function to transform table records
Explains extracting a field from a record using a function
Discusses creating functions for lists similar to 'each' and underscore
Illustrates incrementing list values using a function
Encourages viewers to ask questions for further clarification
Promotes the presenter's DAX and Power Query courses
Transcripts
this has been the most requested video
so far for each and every one of you I'm
going to talk about the each keyword in
power query words would not do any
justice let's just go deep dive and take
a look at what the hell is each in
powerquery let's go when you're trying
to understand the each and the
underscore the each is the easy one
because it means the same like every
single item in a collection but what
does the underscore mean the underscore
on the other hand changes the meaning
depending upon on the context in which
you're using it let's just take a look
at a practical example now I have a
table three columns right here and I
would like to add a column to this table
for that I'm going to use a function
called table. add columns let's just
start with that so table. add column and
I'm going to maybe start to write the
name of the column so let's just say new
the first part of the formula is source
source happens to be the name of the
entire table which is nothing but the
previous step new happens to be the name
of the column that I'm creating and
after that I'm going to say that I would
like to write the each which means every
single item but what does the item mean
right here I'm going to write the
underscore right here and close the
bracket now as soon as I happen to use
the underscore in the context of the
table it simply means every single item
of the table and the item here is
nothing but the row so this is the first
record the second record record means
row and the third record and so on and
so forth now as soon as I commit to this
particular function it's actually going
to create create a new column in here
which is where it'll actually capture
the record so if you peek into this
record right here you're going to see
that the entire data of the first row
has been captured right here now since I
happen to use the underscore in the
context of the table the underscore
meant that you're trying to catch hold
of the record of this particular table
now once we have been able to extract
the record which is right here I could
do anything with that record which is
now stored in this little variable right
here which which is nothing but the
underscore let's just say that I'm
trying to maybe capture like a column of
this particular uh record right here so
the record has got three columns date
sales Dev and amount maybe I'm just
trying to capture the amount right here
so how do I do that there can be another
function which is nothing but let's say
record. field and I'm going to say that
the underscore gives me the record and
I'm going to say that my field is
nothing but the amount and I'm going to
close the bracket and press enter now
what this is going to do is it's going
to go inside of that record which is
stored in the variable underscore and
just pull up the amount and give you the
answer which is nothing but the amount
right here now like I said that the
underscore changes the meaning depending
upon the context let's just take a look
at the each and the underscore in the
context of a
list okay I'm playing with the simple
list right here which has got just five
numbers and I will try to write the each
and the underscore in the context of the
list now let's just create a new step in
here and I will use a list function this
time since I'm working with a list so
there happens to be a function called
list. transform the first part of the
list. transform function is to provide a
list and Source was the step just
previous which contained the five
numbers and that's my list and now let's
just happen to write the each keyword so
let's just say each underscore and press
enter now the underscore here means
every single item of the list which is
not a table so it doesn't mean a record
anymore since the context has changed
this time the underscore is going to
capture every single item of this list
so the first time it's going to capture
one the second time it's going to
capture two the third time it's going to
capture three so on and so forth and as
soon as I press enter nothing changes
it's going to give you the same value
because you just captured that value and
put it right here you did nothing with
that let's just say that for every
single item of the list I would want to
add a one so I could probably say
something like now that the underscore
has captured one maybe I'd like to add
one to that so I'm going to say
something like + one and as soon as I do
that you now have counting from 2 to 6
so the point that I was trying to make
which is very very important is that the
each keyword is not very hard to
understand it means every single item
but the underscore part which refers to
the item is going to change the meaning
depending upon the context in which it
has been used if you're using it in in
the context of a table it means a record
if you're using this in the context of a
list it means every single item of the
list an item of the list is every single
value I hope you understood that we not
done yet the each and the underscore
happens to just be a sugar syntax or
maybe an easy way of writing functions
um that make the syntax of power query a
bit easy to read now let's just take a
look at the same query that we had been
working with in the past which is where
we had the table and we use the table.
add columns function to add a column and
each underscore is the keyword that we
wrote Let's just delete that for just a
bit and to start to read that what the
formula is asking for so if I just put
in comma here you can see that the
table. add columns function is asking me
to give me a column generator as a
function that give me a function that
could generate a column it's not asking
you for the each and underscore here it
is asking you for a function and as a
simple syntax of creating a function we
happened to write the each and
underscore now here let's just start to
add a function let's just see that is
that similar to writing an each and
underscore or not so I'm going to maybe
go in here and first start to declare a
variable and let's just open the bracket
and declare any variable let's just call
the variable R you can call it anything
but for the moment I'm going to call
this as r as soon as you declare the
variable r r is going to hold up every
single item of the table and like I said
every single item of the table is
nothing but the record of the table or
the row of the table so R here means the
row of the table now how do I convert
the R into a function so I'm just going
to maybe start to write an equals to
sign and a greater than symbol this
rocket sign right here means function
anything that you write after that
happens to be a function and that's what
the formula is asking me for so I'm
going to say that hey I don't really
want to do anything just give me the r
back which is nothing but my record back
I'm going to close in the bracket and
press enter and what do I get I get the
same output like I was able to get with
the each and the underscore and the each
and the underscore happens to be an easy
way to read power query syntax but this
happens to be the same and we can do
something with that r r happens to hold
the record value for every single row
and we could do something with it so
let's just say that we would want to
again extract the amount from here let's
just extract it in a slightly different
way so this time I'm going to say that
hey R happens to hold the record the
record has got three columns date sales
D and amount and I would want to extract
the amount so I'm going to say that hey
square bracket and I'm going to say
amount and this time as soon as I press
enter I'm going to get the amount of
that particular value right here awesome
now let's just start to create functions
in a list and let's just see if it also
means the same over there all right I'm
back at my list which is where just a
while ago we had five numbers one
through five and to every single number
we added one using the each and the
underscore keyword let's just happen to
delete the each and the underscore
keyword and let's just start to create a
function let's just quickly verify that
as well so if I just happen to write a
comma again you can see that this is
asking you that hey please give me the
transformation that you would like to
apply as a function so let's just to
start to create a function this time I'm
going to maybe declare the function
variable as X and I'm going to maybe
start to write the function keyword so
which is nothing but the equals to and
the greater than sign this converts this
into a function what has X done X has
captured the value of every single item
of the list whatever the underscore was
doing it earlier the x is doing it now
the only difference is that X is now
used in a function so I'm going to say x
happens to hold every single value I'm
just going to maybe want to have the X
again if I do nothing with it with X I'm
just going to get the values back 1 2 3
4 and 5 but if I happen to let's say add
+ one right here I'm going to get let's
say 2 3 4 5 and six which were just one
incremented from one now what you could
do is that using a function you could
also happen to declare the underscore as
a variable so the variable like I said
could be anything it could be x y z
anything but whatever you happen to
write here is going to hold up the value
of every every single item of that
particular structure that you're working
with table record list just one single
item so this time I'm going to say that
hey xcore +1 which is not going to give
you the same output right here so just
another way of writing it each in
underscore or you could write a function
and you now know both of them all right
that's been it the each and the
underscore in power query let me know
how did you find this one and in case
you have any questions around this
please feel free to drop in a comment
and I will be glad to reply obviously I
trying to use some very simple examples
to demonstrate how the each and the
underscore works but if you happen to
use a very complicated query and you
maneuvering between the table and a list
and other items of power query
structures then each an underscore would
mean different items of that particular
object structure that you're trying to
work with I hope you understood that
part really really well let me know if
you have any questions around this and
I'll be glad to reply in the end a big
shout about my Dax and my power query
courses in case you're starting out with
RBI please feel free to check out my
courses they're going to take you from a
beginner level to a more advanced level
and help you solve more sophisticated
and more advanced problem even of your
own data thanks so much for watching
this and I'll catch you guys in the next
one cheers bye
[Music]
5.0 / 5 (0 votes)