Video: How to rename columns in Power Query that change on each update
Van: Celia Alves - Solve & Excel
Duur: 31:12
Power Query werkt met vaste kolomnamen. Maar als je overzicht steeds de gegevens van de laatste weken/maanden laat zien, of de titel is een datum, dan moet dit eerst omgezet worden naar iets anders.
In deze video worden 2 manieren uitgelegd hoe de kolomnamen aangepast kunnen worden.
It is not uncommon having to deal with reports with column headers related to dates that change each time we get new data. Columns with no constant names in Power Query are an issue because Power Query expects to deal always with the same column names. Unless we teach it how to deal with the unexpected.
Chapters: 0:00 – Introducing the problem 1:34 – Celia introduces herself and her Youtube channel 3:08 – EXAMPLE 1: variable column names like “Sales of Wk of 12/27” 3:31 – Importing data from external Excel File with Power Query and cleasing data 6:00 – Creating Base step 6:40 - Introducing the Function Table.ColumnNames 7:04 - Introducing the Function Table.RenameColumns 9:56 – Applying the Function Table.ColumnNames 10:58 – Selecting specific items from the list of columns names 13:15 – Creating list of new names for columns 14:44 - Introducing the Function List.Zip 15:17 - Applying the Function List.Zip 16:48 – Applying the Function Table.RenameColumns 18:03 – Finishing Query 18:54 – Testing Query 20:36 – EXAMPLE 2: variable column names like “2021-12-27”, Importing data from external Excel File with Power Query and cleasing data 22:03 – Creating Base step 22:23 – Applying the Function Table.ColumnNames 22:45 – Selecting date items from the list of columns names 24:36 – Creating list of new names for columns 25:09 - Applying the Function List.Zip 27:02 – Applying the Function Table.RenameColumns 29:01 - Finishing QueryFuncties: Table.ColumnNames(), Table.RenameColumns(), List.Select(), Text.Contains(), List.Zip()
