cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
JMK2
Regular Visitor

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. 

1 ACCEPTED SOLUTION
AntrikshSharma
Community Champion
Community Champion

@JMK2 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

AntrikshSharma_0-1669468162899.png

 

View solution in original post

3 REPLIES 3
JMK2
Regular Visitor

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

 

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

AntrikshSharma
Community Champion
Community Champion

@JMK2 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

AntrikshSharma_0-1669468162899.png

 

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.

Top Solution Authors
Top Kudoed Authors