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!
Here's a blog post I wrote on this problem showing how to solve it in M:
https://blog.crossjoin.co.uk/2017/09/25/setting-data-types-on-nested-tables-in-m/
HTH,
Chris
Hello cwebb,
Would you, please, be more specific?
I have already tried setting the type in the function like this
= Table.ExpandTableColumn(#"Merged Queries", "Shifts", {"Personnel Number"}, {"Personnel number"}, type text)But it won't works because Table.ExpandTableColumn doesn't accept a fifth parameter.
Table.NestedJoin function also doesn't allow a parameter for setting types.
Can 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
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
I just requested this as a new "Idea". Please vote for it so we can see it added.
https://ideas.fabric.microsoft.com/ideas/idea/?ideaid=a55c3e56-d519-ef11-9899-000d3a0f4263
