Tengo un archivo Excel con la capacidad en horas por mes por máquina en departamento. Este archivo de Excel se actualizará cada mes donde desaparezca el mes más antiguo (a la izquierda) y aparezca un nuevo mes (a la derecha).
| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |
| | | | | | | 4º trimestre de 2023 | 1º trimestre de 2024 | 2º trimestre de 2024 | 3º trimestre de 2024 | 4º trimestre de 2024 | 1º trimestre de 2025 | 2º trimestre de 2025 | 3º trimestre de 2025 | 4º trimestre de 2025 | 1º trimestre de 2026 | |
| Prio | descripción | Departamento | Debreviación | Tiempo de espera | ID de departamento | oct/23 | 23 nov/ | Dic/23 | Ene/24 | feb/24 | Mar/24 | Abr/24 | Mayo/24 | Jun/24 | Jul/24 | Ago/24 | Sep/24 | oct/24 | Nov/24 | Dic/24 | Ene/25 | feb/25 | Mar/25 | Abr/25 | Mayo/25 | Jun/25 | Jul/25 | Ago/25 | Sep/25 | Oct/25 | nov/25 | Dic/25 | Ene/26 | feb/26 | Mar/26 | Notas |
| 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 | | | | Día festivo |
| N | description_3 | Planificación | 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 | | | | |
En PowerBi, quiero anular la dinamización de las columnas de fecha después de la actualización de Excel, pero quiero evitar escribir manualmente los nuevos meses en el editor de consultas de PowerBi.
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"
en este código M, los meses se escriben manualmente. Pregunta ¿cómo hacer esta dinámica por la información de Excel?
