Automate unpivoting of columns following a certain format

[nicplancho]

Hi guys,

I would like to use some kind of automation (RegEx?) to replace those manual entries by an automated format extraction for all YYYY-MM columns.

Here is the manual unpivoting : #"Unpivoted Only Selected Columns" = Table.Unpivot(#"Filtered Rows1", {"2022-01", "2022-02", "2022-03", "2022-04", "2022-05", "2022-06"}, "Date", "Raw hours")

Do you have any clue regarding the way I may automate this please ?

NB: Yes I may also use Table.UnpivotOtherColumns with the "fixed" columns but I believe it's not so robust and thus I am looking for another solution.

Thanks in advance,

Nicolas.

[nicplancho]

Hi Alexis,

Thanks a lot for your support, works like a chram 🙂

Here is what I exactly did based on your suggestion :

#"PivotList" = List.Select(Table.ColumnNames(#"Filtered Rows1"), each Text.StartsWith(_, "202")),
#"Unpivoted Only Selected Columns" = Table.Unpivot(#"Filtered Rows1", #"PivotList", "Date", "Raw hours"),

[AlexisOlson · Accepted solution]

How do you decide which columns need to be pivoted?

Instead of your list {"2022-01", "2022-02", "2022-03", "2022-04", "2022-05", "2022-06"}, you could write something like this

List.Select(Table.ColumnNames(#"Filtered Rows1"), each Text.StartsWith(_, "2022-"))

 But I don't know exactly what condition you actually need instead of the example I gave.