How to bin small decimal values

[brick]

Hello,

I recently tried to create a histogram in Power BI from some data that ranged between 0 and 0.140. I right clicked the data and created a a new group. I selected Group Type to be "bin" and specified a size I wanted. I clicked OK. So, far things seemed fine. The binned data appeared on my Fields section. However, whenever I tried to use this binned data in a table or graph, I always saw this error:

OLE DB or ODBC error: [Expression.Error] We couldn't fold the expression to the data source.

Through many struggles and experiments, I finally came to to believe that the problem is related to trying to bin data with floating point values. I cooked up this work around.

1. Duplicate the column of data.

2. Multiply that column by 1000

3. Duplicate that scaled column of data

4. Round the scaled data to the nearest whole number

5. Create bins on this scaled and rounded data

6. Create a histogram of the scaled data on the Y axis and the bins of the scaled and rounded data on the X axis

This works but I was curious to ask if I have discovered a bug in the software or there is some other way to solve this problem. I am in DirectQuery mode to a Kusto database. Not sure if that is relevant.

[Greg_Deckler]

I cannot recreate this. Seems to work for me. I am on the December 2019 version of Power BI. See attached.

[brick]

Thanks for trying. Not sure what the difference is. My guess would be that you are not using Direct Query and a Kusto connection. I checked my version and found this:

Version: 2.76.5678.782 64-bit (December 2019)

I will also mention that 2 other people I know tried and also reproduced the same error. So, it wasn't just me.

[Greg_Deckler]

Yep, I'm just using Import with an Enter Data query so my suspicion would be that this is some kind of problem with DirectQuery.

[brick · Accepted solution]

It turns out to be a problem in the Kusto connector. Fixes are planned. One work around is to bin it in the query. This works today.

let
    Source = Kusto.Contents("<cluster>", "<database>", "<table> | extend slack_bin=bin(slack, 0.01)", [MaxRows=null, MaxSize=null, NoTruncate=null, AdditionalSetStatements=null])
in
    Source