Cannot import Kusto tables into Power BI desktop

[gaglenn]

I am having difficulty importing a couple of tables from Kusto into Power BI.  The tables are sourced from "Azure Data Explorer (Kusto)".  After specifying the cluster, database and table name, I receive an error stating "Unable to connect. We encountered and error while trying to connect. Details: "We cannot convert the specified value to the specified type."".  

Looking at the table in Kusto Explorer, there doesn't appear to be anything unusual with the datatype of the fields in these tables.

The tables are not particularly big.  None of the numeric fields contain non-numeric characters, or numeric values that are excessively large.  The boolean fields all appear populated with True/False values.  There are occasional null strings in the osEdition field, and the SQLizerParitionIndex field is all null, which it appears to be in all tables.  None of these datatypes are unusual, and are used in other tables that do import properly.  Do you have any ideas why these tables (the two tables are effectively identical in content format) will not import?  Thanks.

[gaglenn]

Thanks Amit.  I was unable to create a query using the "New Source" method of import.  It would fail when loading the preview, and no query for the table was ever created in the editor.

I tried a different method today.  I created a blank query, and the copy/pasted the query string from a know working table into the function box, and substituted in the name of specific table I want to import.

This is what I get:

It is still indicating some kind of error involving data types, but there is no specificity about which data type or which column is problematic.  This table does not contain any complex types, only strings and numerics.  So, I am failing to see where it is getting confused, or how to get more detailed information on what it doesn't like.

[v-achippa]

Hi @gaglenn,

Thank you for reaching out to Microsoft Fabric Community.

Thank you @amitchandak for the prompt response.

The issue here is likely because of power bi trying to auto detect column types and failing when some columns like SQLizerPartitionIndex contain only nulls or when type inference from Kusto metadata causes a mismatch.

Even when using a blank query, power bi still auto applies type conversions unless you explicitly define them. So try to rewrite the query using an explicit .project to define column types like this below in the Advanced editor:

let

    Source = AzureDataExplorer.Contents("https://<cluster>.<region>.kusto.windows.net"),

    DB = Source{[Name="<database>"]}[Data],

    Query = DB{[Query="storport_NvmeNames

        | project

            buildBranch = tostring(buildBranch),

            buildNumber = toint(buildNumber),

            buildRevision = toint(buildRevision),

            buildArch = tostring(buildArch),

            miniportName = tostring(miniportName),

            popSample = toreal(popSample),

            isTestLab = tobool(isTestLab),

            isMSFTOwned = tobool(isMSFTOwned),

            osEdition = tostring(osEdition),

            osSKUName = tostring(osSKUName),

            dateStamp = tostring(dateStamp),

            deviceCount = tolong(deviceCount),

            SQLizerPartitionIndex = toint(SQLizerPartitionIndex)

    "]}[Data]

in

    Query

Replace the <cluster>, <region> and <database> with actual values.

This make sure that power bi will not try to auto fill the types and instead uses the types you specified in the query.

If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it! 

Thanks and regards,

Anjan Kumar Chippa

[gaglenn]

Thanks you for looking into this Anjan.

This is the query I entered into Advanced Query Editor:

let
    Source = AzureDataExplorer.Contents("https://wdgeventstore.kusto.windows.net",
    DB = Source{[Name="BASE"]}[Data],
    Query = DB{[Query="storport_NvmeNamespacePerformance30DayHistogram
        | project
            buildBranch = tostring(buildBranch),
            buildNumber = toint(buildNumber),
            buildRevision = toint(buildRevision),
            buildArch = tostring(buildArch),
            miniportName = tostring(miniportName),
            popSample = toreal(popSample),
            isTestLab = tobool(isTestLab),
            isMSFTOwned = tobool(isMSFTOwned),
            osEdition = tostring(osEdition),
            osSKUName = tostring(osSKUName),
            dateStamp = tostring(dateStamp),
            deviceCount = tolong(deviceCount),
            SQLizerPartitionIndex = toint(SQLizerPartitionIndex)
        "]}[Data])
in
    Query

With this query, I get an error that states "n error ocurrent in the '' query.  Expression.Error: The name 'DB' wasn't recognized. Make sure it's spelled correctly."

Not sure if it is expecting something other than DB as the name there, or the is something else that is generating the error.  I did try "Database" and "database" in place of DB, but still be the error.

Not sure why the nulls in SQLizerPartitionIndex would be problematic here, it is an artifact of the SQLizer that imports the Cosmos streams to Kusto, and is present in all of our Kusto tables.  It has not been an issue importing into Power BI anywhere else, and some of the other tables are a good deal larger than these.

Thank you for your help.  Please let me know if there is something I misinterpreted or misspelled.

[v-achippa]

Hi @gaglenn,

Looks like this error is because of missing closing parenthesis in the Source line.

The correct line should be like below:

Source = AzureDataExplorer.Contents("https://wdgeventstore.kusto.windows.net"),

After adding the closing parenthesis, try refreshing the preview and let us know if the query loads successfully.

Thanks and regards,

Anjan Kumar Chippa

[gaglenn]

Thanks Anjan.  I was having some issues with the parser originally, so I had though the AzureDataExplorer.Contents method needed multiple parameters, like for this table:

= AzureDataExplorer.Contents("https://wdgeventstore.kusto.windows.net", "BASE", "storport_NvmeNamespacePerformance30DayConsolidated", [MaxRows=50000000, MaxSize=67108864000, NoTruncate=null, AdditionalSetStatements=null])

I closed the parenthesis after the path, and removed the one after [Data], so now it looks like this:

= let
    Source = AzureDataExplorer.Contents("https://wdgeventstore.kusto.windows.net"),
    DB = Source{[Name="BASE"]}[Data],
    Query = DB{[Query="storport_NvmeNamespacePerformance30DayHistogram
        | project
            buildBranch = tostring(buildBranch),
            buildNumber = toint(buildNumber),
            buildRevision = toint(buildRevision),
            buildArch = tostring(buildArch),
            miniportName = tostring(miniportName),
            popSample = toreal(popSample),
            isTestLab = tobool(isTestLab),
            isMSFTOwned = tobool(isMSFTOwned),
            osEdition = tostring(osEdition),
            osSKUName = tostring(osSKUName),
            eventDay = tostring(eventDay),
            deviceCount = tolong(deviceCount),
            SQLizerPartitionIndex = toint(SQLizerPartitionIndex)
        "]}[Data]
    in
        Query

I get the following in the Power BI:

So, it is still not happy with it.  Perhaps there is something unusual about the structure of the table in Kusto, but I'm not seeing anything strange in Kusto Explorer.

If I copy just the query portion of that into Kusto Explorer, things look fine:

So, I'm kind of at a loss as to what Power BI is having trouble with.  Note that the dateStamp column was changed to eventDay, on the theory that dateStamp might have been some kind of keyword.  That does not appear to have been the case.  Thank you for your help.

[gaglenn · Accepted solution]

Hi Anjan,

I have made a discovery that seems to resolve the issue.

Instead of going to the Advanced Query Editor, just use the Get Data import template instead.

Put the query text into the Table Name or Azure Data Explorer query field:

Hit OK, and the preview loads as expected:

After this, the table is able to import properly.  Not sure why this is necessary for these tables, as they are relatively simple, but at least it solves the problem.

[amitchandak]

@gaglenn, please open Power Query and verify if there are multiple steps. If yes, check the step where there is an error. Usually happens when Power BI detects the wrong type or converts to the wrong type in "change data type"