Forum Discussion

wetscott's avatar
wetscott
Icon for Helper I rankHelper I
5 years ago

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 ...
Need Help
v-alq-msft's avatar
v-alq-msft
Icon for Community Support rankCommunity Support
5 years ago

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.

  • wetscott's avatar
    wetscott
    Icon for Helper I rankHelper I
    5 years ago

    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]

  • wetscott's avatar
    wetscott
    Icon for Helper I rankHelper I
    5 years ago

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