Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Anonymous
Not applicable

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

@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

AntrikshSharma_0-1669468162899.png

 

View solution in original post

3 REPLIES 3
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

 

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

AntrikshSharma
Community Champion
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

AntrikshSharma_0-1669468162899.png

 

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors