Get proper type from PowerQuery function

[Lucian]

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 = DynamicsNav.Contents(NAVServiceURL, null),
    SelectCompany = Source{[Name=strCompanyName]}[Data],
    BI_G_L_Entry_table = SelectCompany{[Name="BI_G_L_Entry",Signature="table"]}[Data]
in
    BI_G_L_Entry_table

This function will be used in a second query to get data from similar tables for multiple companies from that Navision database.

Invoking the function to test it, will return data from that table with proper types: Text, Integer, Date/Time/Timezone.

But when invoking the function in another query and expanding the resulting table colum will not have the proper types.

The query is this:

let
    Source = fxBI_CompanyName(),
    #"Invoked Custom Function" = Table.AddColumn(Source, "fxBI_G_L_Entry", each fxBI_G_L_Entry([NAV Company Name])),
    #"Expanded fxBI_G_L_Entry" = Table.ExpandTableColumn(#"Invoked Custom Function", "fxBI_G_L_Entry", {"Entry_No", "G_L_Account_No", "Posting_Date", "Document_Type", "Document_No", "Debit_Amount", "Credit_Amount", "Amount"})
in
    #"Expanded fxBI_G_L_Entry"

Is there a way to "propagate" types directly in the step with "Table.ExpandTableColumn" without addind in the last query a "Change Type" step to proper format each column?

Kind Regards,

Lucian

[Anonymous · Accepted solution]

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]

Hi @Lucian,

Maybe you can use Table.ColumnNames to direct extract column name from column fields which stored the table values:

let
    Source = fxBI_CompanyName(),
    #"Invoked Custom Function" = Table.AddColumn(Source, "fxBI_G_L_Entry", each fxBI_G_L_Entry([NAV Company Name])),
    #"Expanded fxBI_G_L_Entry" = Table.ExpandTableColumn(#"Invoked Custom Function", "fxBI_G_L_Entry", Table.ColumnNames(#"Invoked Custom Function"[fxBI_G_L_Entry]{0}))
in
    #"Expanded fxBI_G_L_Entry"

Regards,

Xiaoxin Sheng

[Lucian]

Hello @Anonymous ,

And thank you for suggestion. Unfortunately the "Table.ColumnNames" command would be helpful just for expanding all the column names from that table function (instead of a "short list" of them) but did not preserve the column types.

All of them are of "any type" (ABC123) instead of DateTime od Number so I have to do a "change type" for them.

Any other ideea? I don't understand why invoking function separately will give the proper results but when used inside another query will return generic data.

Kind Regards,

Lucian

[Anonymous]

Hi @Lucian,

Can you please share some dummy data with a similar data structure for test to coding formula? It will be help to coding formula and do further tests.

How to Get Your Question Answered Quickly  

Regards,

Xiaoxin Sheng

[Lucian]

Hi @Anonymous ,

Sorry for delay - it took some time to prepare a test environment 🙂

So, I have created an Azure SQL Database on a test tenant - so will be available only in the next 30 days.

SQL Server: tst-pbi-2003.database.windows.net

Database name: PBI-2003 (is the sample database AdventureWorksLT) and "duplicated" the Product table to simulate the multiple companies from a Navision database:  COMP1$Products and COMP2$Products.

To access the data the user is NAVUser and password is Pa55w.rd  (usuall Microsoft lab password)

The "companies" table I have entered data directly in the PBIX file

#table({"NAV Column Name"}, {{"COMP1"}, {"COMP2"}})

To reproduce the test environment as close as as possible, to get the Product table I have created the function:

(strCompName as text) as table =>
let
    Source = Sql.Database(NAVServer, NAVDBName),
    #"ProductTable" = Source{[Schema="SalesLT",Item=strCompName&"$Product"]}[Data]
in
    #"ProductTable"

Then, to get all the product for all the companies listed in the Companies table, I have used your approach:

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}))
in
    #"Expanded fxProductTable"

When testing the function and inoke manually for COMP1 I have the propoer results:

But on the #"Expanded fxProductTable" line I get:

The complete PBIX file is here: https://1drv.ms/u/s!AusSzc3evzopaBXA-XlLaxxVlNM?e=5f1XzU 

Thank you for your help.

Kind Regards,

Lucian

[Anonymous · Accepted solution]

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

[Lucian]

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 · Accepted solution]

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]

Hi @Anonymous ,

Thank you for your support! 😁

Kind Regards,

Lucian