unpivot only selected columns (date columns)

[shuijgen]

I have an Excel file with per machine in department the capacity in hours per month. This Excel file will be updated every month where the oldest month (on the left) disappears and a new month (on the right) appears.

						Q4 2023	Q1 2024	Q2 2024	Q3 2024	Q4 2024	Q1 2025	Q2 2025	Q3 2025	Q4 2025	Q1 2026
Prio	description	Dept	Debreviation	Wait time	Dept ID	Oct/23	Nov/23	Dec/23	Jan/24	Feb/24	Mar/24	Apr/24	May/24	Jun/24	Jul/24	Aug/24	Sep/24	Oct/24	Nov/24	Dec/24	Jan/25	Feb/25	Mar/25	Apr/25	May/25	Jun/25	Jul/25	Aug/25	Sep/25	Oct/25	Nov/25	Dec/25	Jan/26	Feb/26	Mar/26	Notes
N	description_1	Constr	CONSTR	2	960	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
N	description_2		PM			0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0				Holiday
N	description_3	Planning	PLAN	2	940	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
N	description_4	WVB	PRO	2	950	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0

In PowerBi I want to unpivot the date columns after the Excel update but want to avoid typing manually the new months in PowerBi Query editor.

let
    Source = Excel.Workbook(File.Contents("\\onsvfs111.one.nts-group.nl\Data\NTS-Norma\Drachten\BI dashboards\Forecast\Dynamische capaciteit plan.xlsx"), null, true),
    Capaciteit_bewerkingen_periode_Sheet = Source{[Item="Capaciteit_bewerkingen_periode",Kind="Sheet"]}[Data],
    #"Type gewijzigd" = Table.TransformColumnTypes(Capaciteit_bewerkingen_periode_Sheet,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type any}, {"Column6", type any}, {"Column7", type any}, {"Column8", type any}, {"Column9", type any}, {"Column10", type any}, {"Column11", type any}, {"Column12", type any}, {"Column13", type any}, {"Column14", type any}, {"Column15", type any}, {"Column16", type any}, {"Column17", type any}, {"Column18", type any}, {"Column19", type any}, {"Column20", type any}, {"Column21", type any}, {"Column22", type any}, {"Column23", type any}, {"Column24", type any}, {"Column25", type any}, {"Column26", type any}, {"Column27", type any}, {"Column28", type any}, {"Column29", type any}, {"Column30", type any}, {"Column31", type any}, {"Column32", type any}, {"Column33", type any}, {"Column34", type any}, {"Column35", type any}, {"Column36", type any}}),
    #"Rijen gefilterd" = Table.SelectRows(#"Type gewijzigd", each true),
    #"Changed Type" = Table.TransformColumnTypes(#"Rijen gefilterd",{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type any}, {"Column6", type any}, {"Column7", type any}, {"Column8", type any}, {"Column9", type any}, {"Column10", type any}, {"Column11", type any}, {"Column12", type any}, {"Column13", type any}, {"Column14", type any}, {"Column15", type any}, {"Column16", type any}, {"Column17", type any}, {"Column18", type any}, {"Column19", type any}, {"Column20", type any}, {"Column21", type any}, {"Column22", type any}, {"Column23", type any}, {"Column24", type any}, {"Column25", type any}, {"Column26", type any}, {"Column27", type any}, {"Column28", type any}, {"Column29", type any}, {"Column30", type any}, {"Column31", type any}, {"Column32", type any}, {"Column33", type any}, {"Column34", type any}, {"Column35", type any}, {"Column36", type any}}),
    #"Removed Top Rows" = Table.Skip(#"Changed Type",2),
    #"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Prio", type text}, {"Omschrijving", type text}, {"Afdeling", type text}, {"Bewerking", type text}, {"Wacht tijd", Int64.Type}, {"Afdelings ID", Int64.Type}, {"01/10/2023", Int64.Type}, {"01/11/2023", Int64.Type}, {"01/12/2023", Int64.Type}, {"01/01/2024", Int64.Type}, {"01/02/2024", Int64.Type}, {"01/03/2024", Int64.Type}, {"01/04/2024", Int64.Type}, {"01/05/2024", Int64.Type}, {"01/06/2024", Int64.Type}, {"01/07/2024", Int64.Type}, {"01/08/2024", Int64.Type}, {"01/09/2024", Int64.Type}, {"01/10/2024", Int64.Type}, {"01/11/2024", Int64.Type}, {"01/12/2024", Int64.Type}, {"01/01/2025", Int64.Type}, {"01/02/2025", Int64.Type}, {"01/03/2025", Int64.Type}, {"01/04/2025", Int64.Type}, {"01/05/2025", Int64.Type}, {"01/06/2025", Int64.Type}, {"01/07/2025", Int64.Type}, {"01/08/2025", Int64.Type}, {"01/09/2025", Int64.Type}, {"01/10/2025", Int64.Type}, {"01/11/2025", Int64.Type}, {"01/12/2025", Int64.Type}, {"01/01/2026", Int64.Type}, {"01/02/2026", Int64.Type}, {"01/03/2026", Int64.Type}, {"Opmerkingen", type text}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type1", each ([Bewerking] <> null)),
    #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Afdeling", "Bewerking", "Wacht tijd", "Afdelings ID", "01/10/2023", "01/11/2023", "01/12/2023", "01/01/2024", "01/02/2024", "01/03/2024", "01/04/2024", "01/05/2024", "01/06/2024", "01/07/2024", "01/08/2024", "01/09/2024", "01/10/2024", "01/11/2024", "01/12/2024","01/01/2025", "01/02/2025", "01/03/2025", "01/04/2025", "01/05/2025", "01/06/2025", "01/07/2025", "01/08/2025", "01/09/2025", "01/10/2025", "01/11/2025", "01/12/2025","01/01/2026", "01/02/2026", "01/03/2026"}),
    #"Unpivoted Only Selected Columns" = Table.Unpivot(#"Removed Other Columns", {"01/10/2023", "01/11/2023", "01/12/2023", "01/01/2024", "01/02/2024", "01/03/2024", "01/04/2024", "01/05/2024", "01/06/2024", "01/07/2024", "01/08/2024", "01/09/2024", "01/10/2024", "01/11/2024", "01/12/2024","01/01/2025", "01/02/2025", "01/03/2025", "01/04/2025", "01/05/2025", "01/06/2025", "01/07/2025", "01/08/2025", "01/09/2025", "01/10/2025", "01/11/2025", "01/12/2025","01/01/2026", "01/02/2026", "01/03/2026"}, "Attribute", "Value"),
    #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Only Selected Columns",{{"Attribute", "Datum"}, {"Value", "Capaciteit"}, {"Afdelings ID", "AfdelingsID"}, {"Wacht tijd", "Wachttijd"}, {"Bewerking", "Bewerkingsplaats"}}),
    #"Added Custom" = Table.AddColumn(#"Renamed Columns", "Check", each if DateTime.From([Datum]) >= Date.StartOfMonth(DateTime.LocalNow()) then true else false),
    #"Filtered Rows1" = Table.SelectRows(#"Added Custom", each true),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows1",{"Check", "Afdeling"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Removed Columns",{{"Datum", type date}}),
    #"Added Custom1" = Table.AddColumn(#"Changed Type2", "Custom", each ([Capaciteit] * 52) / 12),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Custom1",{"Capaciteit"}),
    #"Renamed Columns1" = Table.RenameColumns(#"Removed Columns1",{{"Custom", "Capaciteit"}}),
    #"Changed Type3" = Table.TransformColumnTypes(#"Renamed Columns1",{{"Capaciteit", type number}})
in
    #"Changed Type3"

in this M-code the months are typed manually. Question how to make this dynamic by the Excel info?

[Anonymous]

Hi @shuijgen ,

I am glad to hear your problem has been resolved, please kindly mark it as the solution. More people will benefit from it.

Thanks for your efforts & time in advance.

Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[shuijgen · Accepted solution]

a bit of google and found some useful video

 https://www.youtube.com/watch?v=yJGdfh1Fo5E  

= Table.Unpivot(#"Removed Other Columns", List.Select(Table.ColumnNames(#"Removed Other Columns"), each Text.Contains(_,"/",Comparer.OrdinalIgnoreCase)), "Attribute", "Value")

changed a bit to my data and seems to work.