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

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
    Icon for Responsive Resident rankResponsive 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

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

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

        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