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 ...
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.
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]))