Power Query: Avoiding naming column headers to avoid breaks

Queries in Power BI and Excel often break because of a small change to a column header. Many actions refer to these column headers. I go through how to do many common actions without referencing the column headers, including trim, replace values, toGGLe cASe, rename, reorder and even assigning data types. If you subscribe to my channel and request I can transfer over the workbook I used.
There are two options:
No code solution using transpose which does many of the options well but not perfectly
Low code solutions which is more robust and does more. Here are the custom M functions I use:
Create a list of column names: Table.ColumnNames(Source)
Convert values into their type (e.g. date, number etc.): Table.AddColumn(Previous step", "Type", each Value.Type([Column which you want to convert]))
Convert one column to a list: OriginalCol = Previous step [Name of col]
Zipped list with 2 lists: The column name & its type: List.Zip({Col name,Column with data type})
The next 3 I did all in one step at the end:
Transform data types for column headers: Table.TransformColumnTypes()
Apply renamed column headers: Table.RenameColumns()
Which columns to keep: Table.SelectColumns()
00:00 - Introduction
01:03 - Power Query settings
02:27 - No code (transpose) method
02:55 - Trim, cASe & rename
03:39 - Choose columns
04:30 - Reorder
06:14 - Low code (Lizt.zip)
07:13 - Trim, cASe, Filter
08:03 - Data types
09:40 - Bring it together
14:21 - Fix data types

[@tomhaase1386]

I have seen some other solutions, but your was great. I subscribed your channel now. ❤

[@learnspreadsheets]

That’s so nice to read! Yes when I took the time to learn the process combining ideas I made this video so whenever I need to remember it I go back to the video 😃

[@gborka]

Hot topic. Thanks a lot. Avoid all this endless columns headers referencing is a big deal. Keep going you doing a great job.

[@learnspreadsheets]

Thanks man! This was a monster video to make ❤️

[@syrophenikan]

FANTASTIC! I'm going to now practice this on my data to refine it until it's a muscle memory. Great tactic!!!

[@learnspreadsheets]

Yes! It takes a while but totally worth it!

[@franciscocardenas4499]

Great. I've been dealing with this for some time. Thanks to you I can optimize mi files! Great job!

[@learnspreadsheets]

Glad you find it useful 🙃

[@lgendwila]

This is incredibly helpful. Thank you so much for sharing!

[@learnspreadsheets]

Glad you like it!

[@Eternal_Stone]

Awesome tutorial - great work around and excellently explained

[@learnspreadsheets]

That’s so kind! Thanks so much 😃

[@theoriginalinvisible]

Really nice David, thanks!

[@learnspreadsheets]

Glad you liked it

[@cesartozzi]

Thank you very much David. It really helped me.

[@learnspreadsheets]

You are welcome!

[@mtstans]

OMG THANK YOU SO MUCH!

[@FRANKWHITE1996]

subscribed! 🙌

[@learnspreadsheets]

Thanksss, hope you find my content useful!

[@contabilidad_del_futuro]

Great tips, thanks Dave. Could you suggest a video to classify the text based on some keywords from another table? For example, the sentence is "grocery expense", the query finds the word "grocery" and classifies "1 food expense". Another statement "electricity bill" and classify electricity as "household expense." And so.

[@learnspreadsheets]

Hey, thanks for the feedback. Here are some of mind you can try: kzbin.info/www/bejne/jXnbfKl-iN6KmdU kzbin.info/www/bejne/gGnNoqammKareqM and kzbin.info/www/bejne/b4qrco1ud5Z0m9k but unsure if they will do exactly as you request

[@mireyamorales9334]

Genius!

[@learnspreadsheets]

Glad you like it!

[@CorneTraa]

Thanks David this looks great. Could you transfer the workbook you used in the video?

[@learnspreadsheets]

Thanks, unfortunately I don’t have that file I have some from my newer videos but not this one

[@markbaxter1309]

Hi David, really REALLY useful video. I have been trying to find a solution to this type of issue for ages. You mention in the video that you can send a copy of the file if needed. How can I get a copy? - Thanks again.

[@learnspreadsheets]

Heya! Glad you like it! Subscribe to my channel then email me & I’ll try to hunt it down! [e-mail]

[@ScottKeene70]

Thanks! Lot's of very useful tips here. I don't quite understand what's going on in the sorting hack at 04:30 - Reorder. (EDIT - I missed the "Transpose" step!) (You did inspire me to play around with Column From Examples and find some really quick ways to do transformations though!)

[@learnspreadsheets]

Thanks for the feedback! Yeah I love these tricks 🙃. Col by examples has many use cases!!

[@omirek2]

Is there any hack for Group By? I need to make sum across all the columns, grouped by specific column. I have all setup without column references, but this is where I'm getting stuck.

[@learnspreadsheets]

Hmm group by is for rows not columns, maybe you could transpose first & then group by? It’s hard to understand exactly what you need through a short message sorry

[@omirek2]

@@learnspreadsheets Thanks for responding. I mean I'm trying to aggregate rows, yeah - but basically in each column except one. That's why I said all "columns". I can't get away without referencing column names. Maybe there's a way to reference a column by index?

[@cristiancifuentes4149]

Great tips! I started using PowerQuery and your tutorial was very helpful. Any chance you could send me the custom code?

[@learnspreadsheets]

Thanks Cristian. I am offering the files for my newer videos here www.xlconsulting-asia.com/youtube-files.html but unfortunately some of my older videos I wasn’t doing that for so if you can’t find that specific file maybe some others may be helpful there

[@leemarkin]

Dear sir, I have a set of data that don't have headers, I load it to power query and pq automatically read it as header and change it if have same value (eg. 100%2, 100%3), even I demote it in samples I still can't avoid it, any idea?

[@learnspreadsheets]

Power query sometimes automatically promotes headers, this will be a step in your query pane on the right, click the x to delete that step called “promoted headers” and maybe another one that says “changes data type”, if that doesn’t work try this if your dataset is from the same excel workbook. Excel may try to make your data into a table first & load those headers before launching power query. To avoid this don’t use the table & use a named range instead by selecting the relevant rows & columns then type a name in the name box in the top right (in native excel not power query)

[@arunachaleswaran]

Nice!!!!!!!!!!!!!!!!! ... I was looking for this only .. Thanks much.. WIll it work for scenarios where the rows are like 300k ?

[@arunachaleswaran]

I got the reply too!!!! Thanks

[@learnspreadsheets]

Great! It should work with many rows yes!

[@gregsmith2547]

Will this do anything for increasing refresh speed?

[@learnspreadsheets]

This adds more steps so it may show down refreshing sadly, but its really a case by case basis

[@emilenescheepers8089]

Such a useful video thanks! Learning something new every day in PQ Just have one question, if I format my column (like what you did at 3mins into the video), it creates a new column on my side instead of amending the current column I selected. How can I change this?

[@emilenescheepers8089]

I did this workaround in one step thats fine but just wondering why it was creating new columns for every format = Table.TransformColumns(Table.TransformColumns(TransposedTable,{{"Column1", Text.Trim, type text}}),{{"Column1", Text.Proper, type text}})

[@learnspreadsheets]

Heya! So there’s a transform tab and an add column tab. Many commands exist in both but they differ in that regard. The first one will change it in place, the latter will add a new column. The code is different too as you spotted

[@Bhavik_Khatri]

Could you please email a copy of this file?

[@learnspreadsheets]

It’s been a a while but email me & I’ll seee if I can find it. [e-mail]

[@MrHhubi]

Chaotic recording

[@learnspreadsheets]

Thanks for the comment but sorry you feel that way, it’s aimed at those with quite a lot of power query experience already

[@zxccxz164]

This is great, but what i am dying to know is: How can i hover over a COLUMN HEADER and show the Description in of the field from the meta data. Or some other longer description. ie Think baseball....column header (field name) ERA, hover and show Earned Run Average OR Store EARNED RUN AVERAGE as the field name, somehow have a NAME ALT of field as ERA. Be able to show ERA as column header and Earned Run Average as hover over. This is SOOOOOO important to keep good descriptive name conventions AND conserving precious space on the canvas

[@learnspreadsheets]

Hey power query doesn’t let you store metadata about a column unfortunately, you can store that info in a separate table perhaps?

[@zxccxz164]

@@learnspreadsheets there is the description property (also in SSAS tab model), but you can't access it. (or at least in 5 years i have seen no one access it. I suppose you could store in separate table. But still no real way to use it (easily) Some how you can pop up tool tip table, but filtering to the metadata would be difficult?? So annoying that i am wasting thanksgiving wondering why no one else finds this irritating. Oh that and you can't turn on Bookmarks by default in Power BI Report Server.