Forum Discussion

Lucian's avatar
Lucian
Icon for Responsive Resident rankResponsive Resident
6 years ago
Solved

Get proper type from PowerQuery function

Hello, I need some help getting proper type inside a PowerQuery function. To get some data from a Navision table, I have created a function:   (strCompanyName as text) as table => let Source...
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    HI Lucian,

    I test with a few M query functions but failed to nested change type parameter into expanding column function. It seems like you can only extract previous table schema and use it in the next change type steps.

    Custom function to extract the table column name and type structure that can be used in change type function:

     

    let
    RecognizeType=(tb as table) => List.Zip({ Table.Schema(tb)[Name],List.Transform(Table.Schema(tb)[Kind], each Expression.Evaluate("type "&_))})
in
    RecognizeType

     

    Usage:

     

    #"Changed Type"= Table.TransformColumnTypes(#"previous steps",RecognizeType('table'))

     

    Notice:

    1. please do mask with shared sensitive data.

    Regards,

    Xiaoxin Sheng

  • Anonymous's avatar
    Anonymous
    6 years ago

    HI Lucian,

    This issue may be caused by force invoke external or previous steps datasource to calculate in the current step. They may affect the satable of calculation and cause the conflict or compatibility issues when calculate out of the current step and contents.

    You can take a look at the following blog to know more about this issue:

    Data Privacy and the Formula Firewall in Power BI / Power Query 

    Regards,

    Xiaoxin Sheng

Lucian's avatar
Lucian
Icon for Responsive Resident rankResponsive Resident
6 years ago

Hi Anonymous ,

And thank you for your sugestion and patience.

 

I have tried your function in the "consolidation" query as this:

 

let
    Source = Companies,
    #"Invoked Custom Function" = Table.AddColumn(Source, "fxProductTable", each fxProductTable([NAV Column Name])),
    #"Expanded fxProductTable" = Table.ExpandTableColumn(#"Invoked Custom Function", "fxProductTable", Table.ColumnNames(#"Invoked Custom Function"[fxProductTable]{0})),
    #"Changed Type"= Table.TransformColumnTypes(#"Expanded fxProductTable",RecognizeType(REF_SalesLT_COMP1Product))
in
    #"Changed Type"

 

Where "REF_SalesLT_COMP1Product" is the "reference" table for a single company from where the function should get the correct type for each colum. If this was the indended use of your function.

Unfortunately, this way I get an error message:

Formula.Firewall: Query 'CONSOLIDATED_Products_v3' (step 'Changed Type') references other queries or steps, so it may not directly access a data source. Please rebuild this data combination.

The good news is that if I configure report to "Ignore the Privacy levels and potentially improve performance" it seems to be exaaaactly what I need! So, I have to thank you for this ideea! 😀

 

But... Is it possible to avoid the Formula.Firewall error and use the default option "Combine data according to your Privacy Level settings for each source"?

 

Kind Regards,

Lucian

Anonymous's avatar
Anonymous
Not applicable
6 years ago

HI Lucian,

This issue may be caused by force invoke external or previous steps datasource to calculate in the current step. They may affect the satable of calculation and cause the conflict or compatibility issues when calculate out of the current step and contents.

You can take a look at the following blog to know more about this issue:

Data Privacy and the Formula Firewall in Power BI / Power Query 

Regards,

Xiaoxin Sheng

  • Lucian's avatar
    Lucian
    Icon for Responsive Resident rankResponsive Resident
    6 years ago

    Hi Anonymous ,

     

    Thank you for your support! 😁

     

    Kind Regards,

    Lucian