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!

[AnalyticsWizard]

@deanbland 

Unpivoting columns when headers are dynamic (like dates) can be a bit tricky, but Power Query in Power BI has the flexibility to handle such scenarios. Here’s a general approach you can take to unpivot columns based on their position:

1. Load the data into Power Query Editor.

2. Identify how many columns are static and should not be unpivoted (like IDs, names, etc.).

3. After identifying the static columns, go to the 'Advanced Editor' and find the step where the columns are first all selected (usually named `Changed Type` or similar).

4. You’ll need to modify the M code to dynamically select columns based on their position. Here’s a pattern you can follow:

let
Source = ...,
// Other steps...
UnpivotColumns = Table.UnpivotOtherColumns(Source, {"StaticColumn1", "StaticColumn2"}, "Attribute", "Value")
in
UnpivotColumns

Replace `"StaticColumn1", "StaticColumn2"` with the actual names of the static columns you want to keep.

5.  Use the `Table.UnpivotOtherColumns` function to unpivot all columns except the ones you specified. This will automatically include all the dynamically named date columns as the ones to unpivot.

6. Once you've edited the M code, click 'Done' in the Advanced Editor, then 'Close & Apply' in the Power Query Editor to apply these changes to your model.

This method allows you to unpivot columns without having to specify each column by name, which is perfect when dealing with dynamic headers like dates. The key is in step 4, where you only list the static column names, and Power Query takes care of the rest.

Remember, every time the column headers change, the query will still work as it’s not looking for specific column names but rather their relative position after the static columns.

If you find this approach helpful, please consider marking it as the solution and any kudos are always appreciated!

[mathur786]

Unpivot other columns except first 2 columns without knowing the headings of first 2 columns

[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