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!
I can't reproduce this with the build of Excel 2016 that I have, but it could be a bug in an older version of Excel/Power Query.
However, can you confirm that the data type conversion has not taken place in the original query that contains the text values? It's very common that an extra "Changed Type" step is added somewhere and does a data type conversion that you did not want.
Chris
Agree with Chris.
In any case: expanding does not change column data types nor convert values to another data type.
The column data types after expanding, are determined by the data type of the column with nested tables (before expanding).
If you have numbers after expanding, then you also have numbers before expanding.
Birdjo My values are texts, but they are in column with type number, which are 2 different things in Power Query.
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.
