Forum Discussion
Leading zeros getting lost in dataflow
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.
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.