Problem Facing while learning Custom Functions in Power Query

[Kartick0075]

Dear Respected All,
I'm learning Power Query from ExcelIsFun on YouTube. I've learnt a lot of things from Mike Sir. He is truly a genius. I've come to know about this Forum from him.

But, I got stuck while following 2 videos from this channel. (Link 001 :

and Link 002 :

). I failed to understand even after trying lots of times, why did he use "(x) =>" in "Add Column" Dialog Box and delete "each" Word before "MAT"? Please help me. I'm attaching the screenshots below.....

 

[lrobbo314]

When you use the keyword 'each' you can refer to that variable with the shorthand character _. You can omit each by naming the variable like in your example as (x)=>. If you have nested formulas that use the _ character you get stuck in the scope of the current function you're in and can't refer to the outer function. Naming your variables gets around this problem and makes the code clearer and more understandable.

 

[Kartick0075]

When you use the keyword 'each' you can refer to that variable with the shorthand character _. You can omit each by naming the variable like in your example as (x)=>. If you have nested formulas that use the _ character you get stuck in the scope of the current function you're in and can't refer to the outer function. Naming your variables gets around this problem and makes the code clearer and more understandable.

Thank you so much Sir for your information. But, Would you kindly explain what do you mean by "Scope"?

 

[lrobbo314]

RankFunction = (tabletorank as table) as table =>
let
SortRows = Table.Sort(tabletorank,{{"Sales", Order.Descending}}),
AddIndex = Table.AddColumn(SortRows, "newcolumn", each table[column name]+1)
in
AddIndex,

see above how the first function declares the variable as 'table' then below we use the each _. If we used each _ instead of table =>, then there would be no way to reference the table. Because you'd be trying to use _ for both. But _ only refers to the current function you're writing. That's what I mean by scope.

Here's all you need to really know about it. There is really no difference between the two methods. Using each _ is fine when you're using 1 function per line of code, or step. Otherwise, if you have nested functions, i.e. a function within a function, you might need to ditch the each _ and use variable names.

 

[Kartick0075]

RankFunction = (tabletorank as table) as table =>
let
SortRows = Table.Sort(tabletorank,{{"Sales", Order.Descending}}),
AddIndex = Table.AddColumn(SortRows, "newcolumn", each table[column name]+1)
in
AddIndex,

see above how the first function declares the variable as 'table' then below we use the each _. If we used each _ instead of table =>, then there would be no way to reference the table. Because you'd be trying to use _ for both. But _ only refers to the current function you're writing. That's what I mean by scope.

Here's all you need to really know about it. There is really no difference between the two methods. Using each _ is fine when you're using 1 function per line of code, or step. Otherwise, if you have nested functions, i.e. a function within a function, you might need to ditch the each _ and use variable names.

Thank you Sir. You helped me understanding the concept finally. May God bless you!

 

[lrobbo314]

Hope it helped. It was harder to explain than I thought it would be. If you have any questions in the future, let me know and I'll try to help.

 

[Kartick0075]

Hope it helped. It was harder to explain than I thought it would be. If you have any questions in the future, let me know and I'll try to help.

Sir, I've another question, is it possible to do Partial Match Lookup accurately in Power Query? In Excel, it's very easy.

Suppose, you have a Source Table with Company-wise Monthly Revenue. Now in the Destination or Lookup Table, you have to extract the Revenue from the Source Table based on the Partial Company Names in Lookup Table.