Forum Discussion
Leading zeros getting lost in dataflow
I am developing a Power BI Desktop report connected to a dataflow via DirectQuery. One of my dimension tables has a column (Item) with the following values (greatly reduced for demonstration purposes):
| Item (String type) | Description (String type) |
| 000010500 | Sheet Metal Plate 3mm |
| 000010600 | Sheet Metal Plate 4.75mm |
| 10500 | Hammer |
| 10600 | Nitrogen Gas |
| 000011211 | Keyboard |
| 000011212 | Monitor |
The items 000010500 and 10500 are completely different items, but Power BI does not let me set this as a unique key column for the one-side of a relationship because it seems to be ignoring the leading zeros in one of the items, and therefore assuming there are duplicate values.
I noticed this does not happen when connecting to a datamart with the exact same table, or when connecting to the dataflow via import mode. I suspect this is because the dataflow store the tables in .csv files.
Would there be a work around for this problem? Thanks in advance.
Somewhere these values are being changed to numbers and the leading zeros are dropping. They must be forced to be strings in the dataflow itself. If you let it detect the datatype when storing, it will use a numerical type.
11 Replies
Somewhere these values are being changed to numbers and the leading zeros are dropping. They must be forced to be strings in the dataflow itself. If you let it detect the datatype when storing, it will use a numerical type.
Thanks for the answer. I understand that, but besides defining the data type as text in the dataflow (which I already did) what other steps are possible?
You will need to show me the M code for the query. If it isn't working, then something else is going on in the code. The dataflow isn't changing the datatypes without being told to.
It seems that you are experiencing an issue with Power BI Desktop when using DirectQuery to connect to a dataflow. The issue appears to be that Power BI is ignoring the leading zeros in one of your dimension table columns, which is causing it to assume that there are duplicate values.
Unfortunately, I couldn't find any specific information about this issue in my search results. However, one possible workaround could be to create a calculated column in your dimension table that concatenates a prefix (such as "Item") to the Item column values. This would ensure that the values are treated as unique strings, even if they have leading zeros.
For example, you could create a calculated column with the following formula:
```
Item Key = "Item" & [Item]
```This would create a new column with values such as "Item000010500" and "Item10500", which should be treated as unique by Power BI. You could then use this new column as the key column for your relationship.
I hope this helps! Let me know if you have any further questions or if there's anything else I can assist with. 😊
(1) Need a leading zero on a Month with DirectQuery. https://community.fabric.microsoft.com/t5/Desktop/Need-a-leading-zero-on-a-Month-with-DirectQuery/m-p/118381.
(2) Add Leading Zeros to a Number in Power BI Using Power Query. https://radacad.com/add-leading-zeros-to-a-number-in-power-bi-using-power-query.
(3) Usar o DirectQuery no Power BI Desktop - Power BI | Microsoft Learn. https://learn.microsoft.com/pt-br/power-bi/connect-data/desktop-use-directquery.
(4) Diretrizes sobre modelos de DirectQuery no Power BI Desktop. https://learn.microsoft.com/pt-br/power-bi/guidance/directquery-model-guidance.
