How to rename columns in Power Query that change the name on each data updating - T0029

Опубликовано: 04 Октябрь 2024
на канале: Celia Alves - Solve & Excel

22,506

433

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.
__________________
Learn how to AUTOMATE YOUR EXCEL REPORTS in minutes instead of hours without copy-paste or coding: https://snapreportschamp.com/course

Get this ready-to-use Excel solution for 2 and 3 columns of Dependent Dropdown Lists:
https://solveandexcel.ca/dynamic-depe...
---

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 Query

M Code Functions :

Function Table.ColumnNames - https://docs.microsoft.com/en-us/powe...

Function Table.RenameColumns - https://docs.microsoft.com/en-us/powe...

Function List.Zip - https://docs.microsoft.com/en-us/powe...
-------------------

Get access to the EXERCISE FILES by joining our Telegram Group at https://t.me/celiaalvessolveexcel

See Mark Proctor's solution for this problem here:    • Rename columns in Power Query when na...

Subscribe to my NEWSLETTER for exciting news about the Excel world: https://bit.ly/learnfromcelia

Boost your Excel skills with the best Excel books - Mr Excel Book store - https://www.e-junkie.com/ecom/gb.php?...

Learn how to automate your Excel Reports: https://snapreportschamp.com/

Celia Alves - Microsoft MVP & Certified Excel Expert, Solutions Developer
Solve & Excel Consulting - https://solveandexcel.ca
LinkedIn:   / celiajordaoalves
Facebook:   / solveandexcel
Twitter: @celia_excel
Instagram: @solveandexcel
YouTube: https:www//   / celiaalvessolveexcel

#excel #msexcel #exceltraining #msexceltraining #powerquery #powerquerytraining #dataanalysis #snapreports #solveandexcel #automation #reportautomation #VBA