Forum Discussion
Unpivot + unknown columns + data type woes
Hi, I am trying to refactor an existing set of queries to take advantage of query folding, and I've run into trouble with an unpivot step. This step worked in the original (which used custom SQL to join two SQL tables immediately before the unpivot) but not in the second (which joins two other PowerQuery queries immediately before the unpivot). The first query does not use Query Folding and the second one, hopefully, does (or will once I figure it out).
I believe the issue is that, in the original query datatypes had not yet been assigned to the columns, while in my new version, they have already been assigned in a previous step. Do I have the right idea about the reason for this problem? And what do I do about it?
One more thing - there's a hitch: the data source I'm using for the unpivot columns is not stable, and could add new columns at any time. For this reason, I really like the flexibility of the original custom SQL solution, as it allows me to unpivot any number of mystery columns, then filter out the ones I don't want using a name pattern match in a later step. For this reason, I don't want to add any steps that involve manually selecting, deleting, or changing data types on columns as a part of this refactor. The end goal should be to get the same results I got with my original query, but with a faster refresh time.
If there's an easy way to retain columns on name match prior to the unpivot, that should work because all the columns I want to keep DO share a datatype... but it is beyond my M skill at this time.
Original query (works - pivots all my mystery columns):
let
Source = Sql.Database("(my server)", "(my database)", [Query="my query, which uses an inner join", CreateNavigationProperties=false]),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"( my list of 7 columns)"}, "Attribute", "Value"),
... (skip a few steps not pertinent to this question)...
#"Keep all remaining ""Score"" attributes" = Table.SelectRows(#(my previous step)", each Text.Contains([Attribute], "Score"))
in
"Keep all remaining ""Score"" attributes"
New Query (doesn't work - produces datatype error on Unpivot. I haven't gotten to the Select Rows step yet):
let
Source = Table.NestedJoin(#"first half of join", {"(my join key)"}, #"second half of join", {"my join key"}, "second half of join)", JoinKind.LeftOuter),
#"Expanded second half of join" = Table.ExpandTableColumn(Source, "other query - second half of join", { ( my giant list of columns) }
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Expanded other query - second half of join", { (my list of 7 columns) }, "Attribute", "Value")
in
#"Unpivoted Other Columns"
Anyway, this query is used in a number of downstream operations, so if there's a way to speed it up that would be really helpful. And if not, my customers can live with the current refresh time. Thank you!
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)}
7 Replies
Here's what I have so far: I have created a list query that correctly isolates the column names I need based on pattern match. Now I'm trying to pass that list back into the Table.ExpandTableColumn step as the list of columns I want to expand. If I can do that it should indirectly solve my problem because all the columns in the list have the same data type, so they should be acceptable to the unpivot step.
However, I can't seem to make the Table.ExpandTableColumn step accept the list. Any pointers?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)}I copied and pasted out of my real query and edited to change my real column names.. so it may be that not all the syntax is perfectly representative of what I did. However I do think I included the brackets in my query... I'll check that and report back.
It sounds like you think I should be able to pass a list in this query, so if that's the case I'll tweak it until it works.
