Nested Functions in PowerQuery

[JDanielHarvey]

I came across this video on YouTube, where a nested function was used and I can't seem to understand how the nested function's parameters are receiving their values. It makes sense that you can referrence previously stored variables in the parameters but these parameters are just made up and I'm assuming they are receiving their values from the two steps prior respectively but I guess I didn't realize functions could that. Please let me know if I am misunderstanding what is happening in the attached M. 

[MFelix]

Hi @JDanielHarvey ,

Best person to explain this is @ImkeF one of the best (if not the best) user in M language in the community.

@ImkeF  can you help out on this.

[JDanielHarvey]

Ok sounds good @MFelix . As I spent more time reviewing how functions and parameters work last night, it does make sense that (DataTableTemp, n) are receiving their data from previous steps in the sub 'let' query. This seems to be the case for the each function when you do a manually written 'each' function with an outter and inner table such as 

= Table.AddColumn(#"previousStep", "NewColName", (OT) => Table.AddColumn((OT)[NestedTableColumn], "length", (IT) => Text.Length((IT)[column]), Int64.Type))

So in the query above, (OT) receives it's data from the #"previousStep" table which can then be referrenced inside the nested Table.AddColumn but the (IT) referrences the data in the nested table of (OT)[NestedTableColumn]

As far as the query in the image and my question about (DataTableTemp, n) both are receiving their values from DataTableTemp = FindReplaceList and n = Counter. Just a little counter intuitive b/c at first you think you would be able to just call those variables inside the parameters but that's not how parameters work I suppose. 

The only thing I still don't seem to understand now is:

18th line from the image. Output = BulkReplaceValues(DataTable, 0). if n does == Counter - 1 and the result is "ReplaceTable" is provided which is DataTableColumn transformed with all the tranformations shouldn't the last defined step be something like:
Output = BulkReplaceValues(ReplaceTable, n)? Why is in the image, BulkReplaceValues(DataTable, 0)? What is the zero and why DataTable? 

Hopeful that @ImkeF can help shed some light on this 😊

This image to help show the actual data used in the video 

[ImkeF]

Hi @JDanielHarvey ,

not sure I understand your question and unfortunately I'm not able to see the row numbers.

But as you've rightly realized, this is definition of a nested function.

But it also contains a  call / invocation of a function: Output = BulkReplaceValues(DataTable, 0)

There, DataTable will be fed to it with the value of the outer function call ("Changed Type", like from the last image you've posted)

But the 0 is hardcoded as a starting value. The recursive inner function "BuldReplaceValues" will always start with that value and count up from there.

[JDanielHarvey]

@ImkeF Yeah sorry there were no row numbers, I manually counted that lol. I hate the text editor for PowerQuery - pretty sure there is an SDK to write from VisualStudio, at least that's what I heard from either Chris Webb or the guys over at PragmaticWorks but it does seem to be improving. For the sake of other readers, I have updated the image to include some make-shift row numbers with my o' so terrific image editing skills 😅

Interesting... So this isn't unlike a 'first-class functions' or function 'decorators', or at least thinking about it that way helps me to understand the logic of the query. So when the variable 'Output' calls the BulkReplaceValues function, the result is in fact 'ReplaceValue' stored into 'Output' once it's called in Line 23, is that right?

I didn't realize you could use decorator like syntax to referrence functions later in the query - that's pretty awesome! 

Thanks for your help understanding this! 

[ImkeF]

Hi @JDanielHarvey ,

agree that there are some things to improve in the query editor, but you might want to check out the options 😉 

As an external editor I can recommend Visual Studio Code. There is a nice extension and its much more stable than Visual Studio.