Replace column names of nested tables using Power Query | Excel Off The Grid

★ Want to automate Excel? Check out our training academy ★
exceloffthegrid.com/academy
★ Check out the blog post★
exceloffthegrid.com/power-que...
★ About this video ★
Power Query is a nightmare when we want to combine data with different column names, and even worse when those columns are not in the same order. Can we even combine that data? You bet we can!
We're looking at that today; using Power Query to rename columns in nested tables based on a list, without creating errors.
0:00 Introduction
0:22 Scenario
1:58 Promote headers
4:54 Rename columns
8:41 Rename based on a table
10:25 Conclusion
★ Download 30 most useful Excel VBA Macros ebook for FREE ★
exceloffthegrid.com/
★ Where to find Excel Off The Grid ★
Blog: exceloffthegrid.com
Twitter: / exceloffthegrid
#MsExcel

[@IvanCortinas_ES]

Again another great tutorial. The important thing is not to measure how much the person who tells it knows (who has a lot of knowledge), but to know how to tell it in a way that the public finds out. And that totally happens. Then goal accomplished. Congratulations Mark.

[@ExcelOffTheGrid]

Thank You Ivan - that’s very kind of you to say.
I try to share my knowledge in a way that is accessible to others, so hearing that feedback has really made my day 😁

[@kebincui]

Excellent as always👍. Thanks Mark. You can save one step of promoting headers if in the first step if you set the second parameter to of Excel.Workbook to true, Excel.Workbook(File.Contents(FilePath), true, true).

[@ExcelOffTheGrid]

Good point. It one of those things I know, but rarely remember :-)

[@Bhavik_Khatri]

Excellent video.

[@ExcelOffTheGrid]

Thank you very much! 😁

[@user-vt7kf2jz5i]

Hi great video, but in my power query I have an error that pq can't find the "Find" column and MissingField.Ignore is not working in this case. is there a way to bypass this?

[@thebhaskarjoshi]

Can we expect 30 most useful Excel Office Scripts to automating Excel tasks? Like the VBA pdf one.

[@ExcelOffTheGrid]

I’ve not got any plans for that at the moment. Due to the environmental differences between VBA and Office Scripts it’s harder to create universal code examples.
The Office Scripts course includes a Power Automate code library, and 20+ pages of cheat sheets. So there is information out there, but the focus is different.