Forum Discussion

Lucian's avatar
Lucian
Responsive 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 = 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

 

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

8 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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's avatar
      Lucian
      Responsive Resident
      6 years ago

      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