Forum Discussion
Resolver IISet 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!
Advocate VCan you give me more details about the data sources you're working with, and the query you're trying to write? I've just done some testing with the latest version of Power BI Desktop and it seems like Table.NestedJoin() automatically respects the data types of the columns from the tables it's joining, so there's no need to do anything special in code. I don't know whether this is new functionality though.
Chris
Resolver III 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!
- cwebb
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
- MarcelBeug
Community Champion
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.
- MarcelBeug
Birdjo My values are texts, but they are in column with type number, which are 2 different things in Power Query.
- Birdjo
I use the latest version of Power BI. There was a second update this month.
No new step is added.Maybe you think that shouldn't happen or event it's not possible but that's what happened.
- cwebb
Can you post a screenshot of the Query Editor showing the output of the query that gets data from Excel, before you do the merge, please? If possible could you also post the M code for all of your queries too?
Thanks,
Chris
