where can I read about it: Source = tbl,//#"Table1 (2)", ?
Ken Pul's
M is for (Data) Monkey, chapter 21, 'Convert the query into a function'. Internet search for
power query convert query to function does quite well.
First thing to know is that I'm no expert and what I've done there is me experimenting, and what I have done has very likely been superseded by built-in functionality in the PW Editor, namely the right-click of a query in the expanded
Queries panel at the left side of the PQ Editor and the option to
Create Function… . I tried it but got flummoxed by the first thing that popped up:
No parameters found. I've been meaning to explore it because I suspect it will make things a lot easier.
So back to what I did. In the file I attached to msg#3, while editing the
Table1 query, I'd got to the
Grouped Rows stage where I had tables in the
grp column and I needed to do some operations on each of those tables. So I duplicated the
Table1 query which gave me a
Table1 (2) table (only because I want to leave
Table1 untouched) and I clicked on one of the tables in the
grp column which gave me a 2-column, 3-row table. I then right-clicked the
Table1 (2) query in the
Queries panel and chose
Reference. This creates a new query with the code:
Code:
let
Source = #"Table1 (2)"
in
Source
where
#"Table1 (2)" (and
Source) is one of those tables in the
grp column of the
Table1 query. At this point I do some transformations on that small table. As it happens, very little was required (only one line) but it could have been much more, and I ended up with:
Code:
let
Source = #"Table1 (2)",
#"Removed Other Columns" = Text.Combine(Table.SelectColumns(Source,{"Column1"})[Column1],"¬")
in
#"Removed Other Columns"
Then it's a matter of converting that to a function by adding the top line which passes the parameter (the small table) to the code and changing the
Source = #"Table1 (2)" line to use the parameter, which I did with
Source = tbl, and I only commented out the
#"Table1 (2)" so that I could easily revert to editing it as a query for tweaking:
Code:
(tbl)=>
let
Source = tbl,//#"Table1 (2)",
#"Removed Other Columns" = Text.Combine(Table.SelectColumns(Source,{"Column1"})[Column1],"¬")
in
#"Removed Other Columns"
So I chose to use tbl as the parameter name to hold the table in the (tbl)=>, I could have used more variables, and just need to substitute them in the later M code.
I changed the name of the query/function to fnConcat, then used it in Table1 in the Invoked Custom Function step, which is directly after the Grouped Rows step.
I deleted Table1 (2) query since it's no longer needed, and I should really have deleted the comment in the function.
That's it.
That function turned out to be so simple that it wouldn't be difficult to ditch it altogether and incorporate it into the Grouped Rows step as Nick Burns suggests.
I could also have streamlined the function itself and the rest of the code but I was lazy!
