Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Pass name of function to a function - NOT as text

Greetings, 

I am working on a PQ function to pull back the metadata for a chosen function (user generated, or #shared).  I am aware I can just as easily click in the box that says Function after running the #shared query, though, the client does not want to do that. 

If I hard code the value without quotes, the function works properly, however if I try to pass the value from a function, the pq engine treats the value as text. I tried having the parameter value as: text, any, record, and table, none worked. I have a feeling there is a solution, however, I am stumped at this point. I do appreciate any and all assistance, thank you.

Code that works: 

 

(FunctionName as text) =>
// ^^ * I also tried as any, still evaluates as text, as table and as record did as well.

let
    
    Source = Value.Metadata(Value.Type(MyFunctionName)), // << - Hard Coded here, this works fine

    ConvertToTable = Record.ToTable(Source),

    PivotName = Table.Pivot(ConvertToTable, List.Distinct(ConvertToTable[Name]), "Name", "Value"),

    ExpandExamplesToRecord = Table.ExpandListColumn(PivotName, "Documentation.Examples"),
    
    ExpandExamplesToText = Table.ExpandRecordColumn(ExpandExamplesToRecord, "Documentation.Examples", {"Description", "Code", "Result"}, {"Documentation.Examples.Description", "Documentation.Examples.Code", "Documentation.Examples.Result"})

in 
    ExpandExamplesToText

 

 
Code that doesn't work

 

(FunctionName as text) =>
// ^^ as above, I did try other types to no avail


let
    
    Source = Value.Metadata(Value.Type(FunctionName)), // Passing the value from the parameter, encapsulates in "", so MyFunctionName evaluates as "MyFunctionName", which does not. 

    ConvertToTable = Record.ToTable(Source),

    PivotName = Table.Pivot(ConvertToTable, List.Distinct(ConvertToTable[Name]), "Name", "Value"),

    ExpandExamplesToRecord = Table.ExpandListColumn(PivotName, "Documentation.Examples"),
    
    ExpandExamplesToText = Table.ExpandRecordColumn(ExpandExamplesToRecord, "Documentation.Examples", {"Description", "Code", "Result"}, {"Documentation.Examples.Description", "Documentation.Examples.Code", "Documentation.Examples.Result"})

in 
    ExpandExamplesToText

 

 
Thank you. 

Also, a shout out to ImkeF  and @bengribaudo, whose code got me this far. 

  • Anonymous Try this:

    ( FunctionName as text ) =>                                                                                      
        let
            SharedLibrary = #shared,
            ToTable = Record.ToTable ( SharedLibrary ),
            GetFunction = Table.SelectRows ( ToTable, each [Name] = FunctionName )[Value]{0},
            Source = Value.Metadata ( Value.Type ( GetFunction ) ),                                                                                                                      
            ConvertToTable = Record.ToTable ( Source ),
            PivotName = 
                Table.Pivot (
                    ConvertToTable,
                    List.Distinct ( ConvertToTable[Name] ),
                    "Name",
                    "Value"
                ),
            ExpandExamplesToRecord = Table.ExpandListColumn ( PivotName, "Documentation.Examples" ),
            ExpandExamplesToText = 
                Table.ExpandRecordColumn (
                    ExpandExamplesToRecord,
                    "Documentation.Examples",
                    { "Description", "Code", "Result" },
                    {
                        "Documentation.Examples.Description",
                        "Documentation.Examples.Code",
                        "Documentation.Examples.Result"
                    }
                )
        in
            ExpandExamplesToText

     

3 Replies

  • AntrikshSharma's avatar
    AntrikshSharma
    Community Champion

    Anonymous Try this:

    ( FunctionName as text ) =>                                                                                      
        let
            SharedLibrary = #shared,
            ToTable = Record.ToTable ( SharedLibrary ),
            GetFunction = Table.SelectRows ( ToTable, each [Name] = FunctionName )[Value]{0},
            Source = Value.Metadata ( Value.Type ( GetFunction ) ),                                                                                                                      
            ConvertToTable = Record.ToTable ( Source ),
            PivotName = 
                Table.Pivot (
                    ConvertToTable,
                    List.Distinct ( ConvertToTable[Name] ),
                    "Name",
                    "Value"
                ),
            ExpandExamplesToRecord = Table.ExpandListColumn ( PivotName, "Documentation.Examples" ),
            ExpandExamplesToText = 
                Table.ExpandRecordColumn (
                    ExpandExamplesToRecord,
                    "Documentation.Examples",
                    { "Description", "Code", "Result" },
                    {
                        "Documentation.Examples.Description",
                        "Documentation.Examples.Code",
                        "Documentation.Examples.Result"
                    }
                )
        in
            ExpandExamplesToText

     

  • Anonymous's avatar
    Anonymous
    Not applicable

    You are incredible, AntrikshSharma , thank you so much.  It worked as designed, now I need to disect it a little more so I fully understand the modifications you made

     

    • AntrikshSharma's avatar
      AntrikshSharma
      Community Champion

      Anonymous Awesome! In that case please mark my answer as the solution. Thanks!