Forum Discussion
Unpivot + unknown columns + data type woes
Hi McSarah
So you are here in this step, and my giant list of columns is your column name list? You don't wrap it with {} as it is already a list, you can directly reference it or #"my giant list of columns" if it does have space...also seems missing closing ) and ,
#"Expanded second half of join" = Table.ExpandTableColumn(Source, "other query - second half of join", { ( my giant list of columns)}
Hi McSarah
So you are here in this step, and my giant list of columns is your column name list? You don't wrap it with {} as it is already a list, you can directly reference it or #"my giant list of columns" if it does have space...also seems missing closing ) and ,
#"Expanded second half of join" = Table.ExpandTableColumn(Source, "other query - second half of join", { ( my giant list of columns)}
Anonymous , thanks for your help yesterday. Your syntax fix worked to let me expand the list columns... but now I have an unanticipated problem - I THOUGHT all the columns I needed to unpivot were of the same datatype, but they're not - I have one double mixed in with all the ints. So I'm back to my original issue.
I tried to use the same column list to dynamically convert all the columns to double, and actually, it appears to have worked because the type icon at the top of each target column changed as expected (123 changed to 1.2). However, I still get the same error message when I perform the unpivot:
The type of column "(original double column)" conflicts with the type of other columns specified in the UNPIVOT list.
Here's the (working?) code I used to attempt to change all the UNPIVOT columns to double prior to the UNPIVOT. Is "number" the wrong type to call here?
#"Changed Type" = Table.TransformColumnTypes(
#"My Expanded Columns",
List.Transform(
#"My Column List",
each {_, type number}
)
)
Hi McSarah
I don't see anything wrong with this step, do you have any error? Or actually you have trouble with your next step, how did you do the Unpivot?
Yes, the problem is with the subsequent step - the step I shared above appears to work, but the subsequent unpivot still thinks it's a problem that the original column is a double. I thought maybe "number" wasn't specific enough in the conversion?
I eventually "solved" this problem by converting the double to a currency type (the unpivot appears to accept currency + int). However, this step breaks query folding and is not dynamic in case of future datatype conflicts. So it works for now but is not ideal.