Forum Discussion
Set column type while expanding from merged query
I am working with Excel using Excel.Workbook(Web.Contents())
In matter of fact the type doesn't matter untill I don't lose the zeros. Like 0003 > 3, 0005 > 5.
MarcelBeug the change of the data type occurs while expanding and zeros are lost. I guess there's nothing to get them back.
It is very nice how you values remain their zeros while their type is a number.My temporary solution was to change the type to a number everywhere and duplicate the column with type text in the table that I visualize. Works pretty good but it would be better if that was not necessary.
Thank you both!
How is this true?
Because before expanding, the column type is "text" and after expanding it is "any" data type (and reomves leading 0's).
Is it possible to disable this, because it messes things up significantly.
Or do I have to polute my dataset with a string so that power bi doesn't mess with it?
For anyone coming here from 2023
I have not managed to find a solution to this in Power Bi and I'm pretty sure there is nothing that can be done to fix this.
The only solution that seems to work correctly is by using either R, Python or javascript and manually padding the values in the column.
So in my instance it woulde be python:
dataset['column'] = dataset['column'].apply('{:0>4}'.format)
As for Power Query changing the type of the column, again python to the rescue.
Just put the entire dataset in a pandas dataFrame and set the column types manually the way you want it.