Dynamic Column Names

Power Query
1

Hi,

I have some trouble with dynamic names. My input sheet will change names from one month to the other. I know I can use Table.ColumnNames, but I can’t get it to work when I have multiple columns.

So basically my question is: How do I change:

= Table.RemoveColumns(#"Promoted Headers1",{"jul-17", "aug-17", "sep-17", "okt-17", "nov-17", "dec-17", "jan-18", "feb-18", "mar-18", "apr-18", "maj-18", "jun-18", "jul-18", "aug-18", "sep-18", "okt-18", "nov-18", "dec-18", "jan-19", "feb-19", "01-03-2019", "a", "b", "c", "Total", "Total_1", "Column106", "Column107", "Column108", "Column109", "Column110"})

To a statement that looks at the column reference instead of the dynamic name?

Thanks in behalf

2

Hi,
Suggest you read the book “Collect, Combine and Transform Data …” by Gil Raviv.
There is a great section about this type of problem and how to resolve it. Exercise 4-7 shows how to use a conversion table and M language (Table.TransformColumnNames).
Paul

Enterprise%20DNA%20Expert%20-%20Small

3

Thanks Paul
I was hoping though, not to read a whole book… even though I think I will buy it anyway… it sounds promising.
However, I found a solution to my problem… sometimes it is good to take a look at a problem with fresh eyes. I simply removed the columns before promoting headers, in that way they are represented with column references… how silly of me. :slight_smile:

4

Great, it was something I noticed, but I thought that you were referring to changing header names more dynamically in general, in which case you would need a more robust and lasting solution. As for the book, I am now comfortable in DAX and found that there is a lot to win for your models when you understand more about the Power Query editor.

Enterprise%20DNA%20Expert%20-%20Small

1 Like
5

I’m going to second @Paul suggestion on Gil’s book. I have used many things from that book. Some you can use directly, other’s at least gives you place to start. What you are talking about can be done and Gil covers it. So if you are at all interested in this stuff, I’d pick it up. Well worth it since Power BI is like an ecosystem. DAX is great but complex DAX can be slow, hard to debug and prone to errors, so what use a tool like Power Query to make that easier?

Nick

Enterprise%20DNA%20Expert%20-%20Small

1 Like
6

Is this the book name?

Collect, Combine, and Transform Data Using Power Query in Excel and Power BI (Business Skills) 1st Edition by Gil Raviv

7

yes it is as per number 2 of 6