Forum Discussion
Nested Functions in PowerQuery
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.
5 Replies
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.
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 videoHi 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.
