Unpivot + unknown columns + data type woes

[McSarah]

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!

[McSarah]

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?

[Vera_33 · Accepted solution]

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)} 

[McSarah]

@Vera_33 , 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}
        )
    )

[Vera_33]

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?

[McSarah]

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.

[McSarah]

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.

[McSarah]

And it was that simple. Amazing! Thank you!

Edit: I simply removed the brackets that I erroneously thought I needed. The list passed to the query as intended once I got the syntax right.