Unpivot a column based on its location, not name

[deanbland]

Hi, 

I am trying to unpivot columns based on their position as the column headers are dynamic dates that change each month. 

How would I go about this?

Thank you!

[deanbland]

Hi @v-shex-msft , 

The basis of what you have done is correct, I just meant to unpivot them, but within the M query, not to use the column headers. 

The column headers change each month and so this would caue problems and so I would need to unpivot the columns without using the column header. 

Hope this makes a bit more sense? 

Regards, 

Dean

[v-shex-msft]

HI @deanbland,

Yes, it is possible, you may need to check the steps of getting and recognize data on your query table to remove the 'change data type' and 'promote header' operations.
Then you can refer to my sample formulas steps as I share to convert your records to date category with position attribute and correspond values.
Regards,

Xiaoxin Sheng

[v-shex-msft]

Hi @deanbland,

Can you please share some dummy data with a similar data structure and expected results? It should help us clarify your scenario and test to coding formula.

How to Get Your Question Answered Quickly  

Regards,

Xiaoxin Sheng

[amitchandak]

@deanbland ,Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[deanbland]

Hi @v-shex-msft & @amitchandak 

The dataset looks like this: 

01/01/2022	01/02/2022	01/03/2022	01/04/2022	01/05/2022	01/06/2022	01/07/2021	01/08/2021	01/09/2021	01/10/2021
0	0	0	0	0	0	1	1	1.2	0.36
1	0	0	0.99	0	0.59	0.63	1	1.58	0.25
0	0	0	0.32	0	0.66	0.48	1	0.32	0.99
0	0	0	0	1	0.94	1.23	1	0.08	1.35

As ststed above, I want to unpivot these columns by referencing their position within the table. 

Thank you! 

Dean

[v-shex-msft]

HI @deanbland,

Did you mean to transform your records to group them based on date and unpivot based on different positions?

If that is the case, you can take a look at the following formulas: (I transpose these records to vertical and unpivot on position fields)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bY7ZDQAhCER78duwAspqLcb+21jFIyRrIpE3DEetLuDTHwUi5xXIAluIFpIFsfAOwAXZQjGAYULz/YYhXQJ3gM4GFrWjtUEpJ02agvDpS1kVSv89wHRSEf1iXo27OIbfDpyeEudtfJSgA4D7uvYB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t, Column6 = _t, Column7 = _t, Column8 = _t, Column9 = _t, Column10 = _t]),
    #"Transposed Table" = Table.Transpose(Source),
    #"Renamed Columns" = Table.RenameColumns(#"Transposed Table",{{"Column1", "Date"}, {"Column2", "P1"}, {"Column3", "P2"}, {"Column4", "P3"}, {"Column5", "P4"}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Renamed Columns", {"Date"}, "Attribute", "Value")
in
    #"Unpivoted Columns"

Regards,

Xiaoxin Sheng