Forum Discussion
Pass Function Name to another Function to use
Hi All,
I've dumped all the Sample Querys/Files/etc from my pbix in favour of a handful of function capable of getting each unique data source ready to be combine.
On my combined tables, I run a series of steps that I want to run on every source, prior to doing any query specific steps. I want to save these steps as another Function.
What I'm trying to work out is how do I pass a Function name to this standard function?
xlsx1row Function
= (ExcelFile as binary) => let
Source = Excel.Workbook(ExcelFile, null, true),
#"FirstSheet" = Source{0}[Data],
#"Promoted Headers" = Table.PromoteHeaders(#"FirstSheet", [PromoteAllScalars=true])
in
#"Promoted Headers"
StandardFunction ?? This is just my current query. But what I want to do is keep to #"Level 5" in each query, but then use #"Rename Source Column" to #"Expanded Data Files" as a function.
let
Source = SharePoint.Contents(SharepointSite, [ApiVersion = 15]),
#"Level 1" = Source{[Name="Shared Documents"]}[Content],
#"Level 2" = #"Level 1"{[Name="Branch"]}[Content],
#"Level 3" = #"Level 2"{[Name="PowerBI Dashboards"]}[Content],
#"Level 4" = #"Level 3"{[Name="DataSources"]}[Content],
#"Level 5" = #"Level 4"{[Name="Profit Center Report"]}[Content],
#"Rename Source Column" = Table.RenameColumns(#"Level 5", {"Name", "DataDate"}),
#"Keep Only DataDate & Content" = Table.SelectColumns(#"Rename Source Column", {"DataDate", "Content"}),
#"Extracted Date" = Table.TransformColumns(#"Keep Only DataDate & Content", {{"DataDate", each Text.Start(_, 8), type text}}),
#"Changed Type" = Table.TransformColumnTypes(#"Extracted Date",{{"DataDate", type date}}),
#"Added ReportingDate" = Table.AddColumn(#"Changed Type", "ReportingDate", each Date.EndOfMonth(Date.AddMonths([DataDate], -1)), type date),
#"Invoke BW Function" = Table.AddColumn(#"Added ReportingDate", "DataFile", each #"Excel"([Content])),
#"Removed Content" = Table.SelectColumns(#"Invoke BW Function",{"DataDate", "ReportingDate", "DataFile"}),
#"Get DataFile Column Names 1" = Table.AddColumn(#"Removed Content", "GetColumnNames", each Table.ColumnNames([DataFile])),
#"Get DataFile Column Names 2" = Table.SelectColumns(#"Get DataFile Column Names 1",{"GetColumnNames"}),
#"Get DataFile Column Names 3" = Table.ExpandListColumn(#"Get DataFile Column Names 2", "GetColumnNames"),
#"Get DataFile Column Names 4" = Table.Distinct(#"Get DataFile Column Names 3"),
#"Expanded Data Files" = Table.ExpandTableColumn(#"Removed Content", "DataFile", #"Get DataFile Column Names 4"[GetColumnNames])
in
#"Expanded Data Files"
My Desired Query I'm hoping for is something like this:
Source = SharePoint.Contents(SharepointSite, [ApiVersion = 15]),
#"Level 1" = Source{[Name="Shared Documents"]}[Content],
#"Level 2" = #"Level 1"{[Name="Branch"]}[Content],
#"Level 3" = #"Level 2"{[Name="PowerBI Dashboards"]}[Content],
#"Level 4" = #"Level 3"{[Name="DataSources"]}[Content],
#"Level 5" = #"Level 4"{[Name="Profit Center Report"]}[Content],
#"SetupTable" = (Call "StandardFunction", tell it to use "xlsx1row" as the transform before combining method)
Hopefully this all makes sense. I'm sure it's deceptively simple, I'm just not seeing it 😞
3 Replies
Hi, wetscott
You may create a function like below.
Func:
(tab as table)=> let #"Rename Source Column" = Table.RenameColumns(tab, {"Name", "DataDate"}), #"Keep Only DataDate & Content" = Table.SelectColumns(#"Rename Source Column", {"DataDate", "Content"}), #"Extracted Date" = Table.TransformColumns(#"Keep Only DataDate & Content", {{"DataDate", each Text.Start(_, 8), type text}}), #"Changed Type" = Table.TransformColumnTypes(#"Extracted Date",{{"DataDate", type date}}), #"Added ReportingDate" = Table.AddColumn(#"Changed Type", "ReportingDate", each Date.EndOfMonth(Date.AddMonths([DataDate], -1)), type date), #"Invoke BW Function" = Table.AddColumn(#"Added ReportingDate", "DataFile", each #"Excel"([Content])), #"Removed Content" = Table.SelectColumns(#"Invoke BW Function",{"DataDate", "ReportingDate", "DataFile"}), #"Get DataFile Column Names 1" = Table.AddColumn(#"Removed Content", "GetColumnNames", each Table.ColumnNames([DataFile])), #"Get DataFile Column Names 2" = Table.SelectColumns(#"Get DataFile Column Names 1",{"GetColumnNames"}), #"Get DataFile Column Names 3" = Table.ExpandListColumn(#"Get DataFile Column Names 2", "GetColumnNames"), #"Get DataFile Column Names 4" = Table.Distinct(#"Get DataFile Column Names 3"), #"Expanded Data Files" = Table.ExpandTableColumn(#"Removed Content", "DataFile", #"Get DataFile Column Names 4"[GetColumnNames]) in #"Expanded Data Files"Then you may try the following query to see if it helps.
let Source = SharePoint.Contents(SharepointSite, [ApiVersion = 15]), #"Level 1" = Source{[Name="Shared Documents"]}[Content], #"Level 2" = #"Level 1"{[Name="Branch"]}[Content], #"Level 3" = #"Level 2"{[Name="PowerBI Dashboards"]}[Content], #"Level 4" = #"Level 3"{[Name="DataSources"]}[Content], #"Level 5" = #"Level 4"{[Name="Profit Center Report"]}[Content], #"SetupTable" = Func(#"Level 5") in #"SetupTable"Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thanks Allan,
Unfortunately I'm getting the below error trying this.
An error occurred in the ‘Standard’ query. Expression.Error: We cannot convert the value "Excel" to type Function.
Details:
Value=Excel
Type=[Type]I've gotten closer. I've got all my steps in a function, and I can call that function to build my starting table.
What I'm missing now though is the ability to define what "combine" function to use.
My standard setup function is now:
= (SourceName as text, combinemethod as function) => let
Source = Table.SelectColumns(ParentDirectory{[Name=SourceName]}[Content], {"Name", "Content"}),
#"Rename Source Column" = Table.RenameColumns(Source, {"Name", "DataDate"}),
#"Keep Only DataDate & Content" = Table.SelectColumns(#"Rename Source Column", {"DataDate", "Content"}),
#"Extracted Date" = Table.TransformColumns(#"Keep Only DataDate & Content", {{"DataDate", each Text.Start(_, 8), type text}}),
#"Changed Type" = Table.TransformColumnTypes(#"Extracted Date",{{"DataDate", type date}}),
#"Added ReportingDate" = Table.AddColumn(#"Changed Type", "ReportingDate", each Date.EndOfMonth(Date.AddMonths([DataDate], -1)), type date),
#"Invoke BW Function" = Table.AddColumn(#"Added ReportingDate", "DataFile", each combinemethod([Content])),
#"Removed Content" = Table.SelectColumns(#"Invoke BW Function",{"DataDate", "ReportingDate", "DataFile"}),
#"Get DataFile Column Names 1" = Table.AddColumn(#"Removed Content", "GetColumnNames", each Table.ColumnNames([DataFile])),
#"Get DataFile Column Names 2" = Table.SelectColumns(#"Get DataFile Column Names 1",{"GetColumnNames"}),
#"Get DataFile Column Names 3" = Table.ExpandListColumn(#"Get DataFile Column Names 2", "GetColumnNames"),
#"Get DataFile Column Names 4" = Table.Distinct(#"Get DataFile Column Names 3"),
#"Expanded Data Files" = Table.ExpandTableColumn(#"Removed Content", "DataFile", #"Get DataFile Column Names 4"[GetColumnNames])
in
#"Expanded Data Files"I'm getting stuck on line #"Invoke BW Function"
If I delete "combinemethod as function" and for #"Invoke BW Function" call the function directly with:
#"Invoke BW Function" = Table.AddColumn(#"Added ReportingDate", "DataFile", each Excel([Content]))
Everything works.
But what I want to do is be able to swap out "Excel" with various functions.
Was thinking something like Function.Invoke? But tried this one with no luck.
#"Invoke BW Function" = Table.AddColumn(#"Added ReportingDate", "DataFile", each Function.Invoke(combinemethod, [Content]))
