Forum Discussion

dvp's avatar
dvp
Helper I
5 years ago
Solved

Azure Data Explorer / Kusto with dynamic column

I have the following function in ADX/Kusto:

 

.create-or-alter function with (docstring = "Customers View",folder = "Views") CustomersTest {
    datatable (CustomerId:string, StateReasons:dynamic) [
        "ee1eaead-5f6d-4371-80cb-7881679f8116", dynamic(["Reason1", "Reason2"]) ]
    | extend StateReasons[0], StateReasons[1]
}
 
When I try to display e.g. CustomerId and StateReasons (or StateReasons_0, StateReasons_1) using DirectQuery in Power BI Desktop I get the following cryptic error:
 

Release:
December 2020

Product Version:
2.88.1385.0 (20.12) (x64)

Error Message:
OLE DB or ODBC error: [Bad request] Request is invalid and cannot be executed..

 

Any clue how to resolve this one?

Need Help
  • dvp's avatar
    dvp
    5 years ago

    There seem to be 2 solutions to the issue:

     

    1) Replace

     

     | extend StateReasons[0], StateReasons[1]

     

    with 

     

     | extend tostring(StateReasons[0]), tostring(StateReasons[1])

     

    in the function

     

    2) Add a Data Transformation step in Power BI Desktop and set the data type of the 2 columns to Text ...

2 Replies

  • v-easonf-msft's avatar
    v-easonf-msft
    Community Support
    5 years ago

    Hi , dvp 

    Sorry I can't fix it on my side,it is recommended to open a support ticket to let engineers look into the issue on your side.

     

     

    Best Regards,
    Community Support Team _ Eason

  • dvp's avatar
    dvp
    Helper I
    5 years ago

    There seem to be 2 solutions to the issue:

     

    1) Replace

     

     | extend StateReasons[0], StateReasons[1]

     

    with 

     

     | extend tostring(StateReasons[0]), tostring(StateReasons[1])

     

    in the function

     

    2) Add a Data Transformation step in Power BI Desktop and set the data type of the 2 columns to Text ...