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.
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 to have worked when I explicitly set the data type to text in the query, even though it came originally as text from the data source before. Now I am able to define this as the one-side of a relationship. It's doesn't make much sense and maybe I am missing something, but it worked.
However, let's say I need to refer to this dimension table to get the list price for an item and multiply it by the quantity sold that resides in a fact table. I would use something like this:
Measure = f_fact[Quantity Sold] * RELATED(d_Item[List Price])
I am not being able to do this. Power BI returns the error "The column 'd_Item[List Price]' either doesn't exist or doesn't have a relationship to any table available in the current context."
The column does exist and the tables have a valid relationship. Also, I looked it up and RELATED is supported by Direct Query. The relationship seems to work when building visualizations in the report (d_Item filters f_fact), but somehow when running the DAX query the relationship is invalid.
I really don't know what's going on.
I actually don't understand that error either as you shouldn't have been able to enter a measure like that. You entered a raw field - f_fact[Quantity Sold]. You cannot do that in a measure and the proper error would be along the lines of unable to determine a single value for that field.
You need to use an iterator. Try this:SUMX( f_fact, f_fact[Quantity Sold] * RELATED(d_Item[List Price]) )
The iterator generates row context and RELATED() understand that, and can combine that with the model's filter context to get the correct values. Without the iterator, there was no row context, only filter context. And RELATED had no clue what to do.
As to the data type issue you never shared your M code and I don't know what the data source is. Not all data sources explicitly type their data or convey that info to Power Query if they do. SQL Server does, CSV files do not. There are 100+ different sources and some do, most don't. You never shared screenshots of the data either. Was the data type set as ABC or was it ABC/123. If the latter, then Power BI didn't know what it was and left it as the "Any" type, which IMHO should be an error and not allowed. Always explicitly type your data. Every single column. Never leave it up to Power BI to guess.