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
07:13 - Trim, cASe, Filter
08:03 - Data types
09:40 - Bring it together
14:21 - Fix data types

[syrophenikan]

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

[gborka]

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

[tomhaase]

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

[franciscocardenas]

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

[Eternal_Stone]

Awesome tutorial - great work around and excellently explained

[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.

[CorneTraa]

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

[arunachaleswaran]

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

[omirek]

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.

[cristiancifuentes]

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

[gregsmith]

Will this do anything for increasing refresh speed?

[lgendwila]

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

[cesartozzi]

Thank you very much David. It really helped me.

[markbaxter]

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.

[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?

[ScottKeene]

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!)

[emilenescheepers]

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?

[Bhavik_Khatri]

Could you please email a copy of this file?

[zxccxz]

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 important to keep good descriptive name conventions AND conserving precious space on the canvas