topic Re: How to Extract just column headers from a table in Power BI? in DAX Commands and Tips

How to Extract just column headers from a table in Power BI?

glyn1997 — Thu, 06 Jun 2024 20:56:30 GMT

Basically, I have 2 identical tables. 1 table contains old product data and the other contains new product data.

The first column contains the products and has the header 'Products'. The other columns contain sales data by month and have headers such as '202201', '202202', '202203' etc all the way up to '202405'...

Although, the old data table month range is -1 compared to the new data table. So the 2 tables have a couple of monthly columns that do not match.

I want to be able to extract the column headers from both tables, and create an inner join so that only MATCHING column names between the 2 tables are returned.

I want this to be done using DAX ideally, as I need it to be an automated process each month, so that I dont have to go into PowerQuery and extract them manually every single month.

Next month, there will be new data and the current new data table will then be the old data table if that makes sense. So I need this to be an automated process so I dont have to do anything manually each month.

If anyone knows how I can achieve this, would be amazing. Thank you 😊

Re: How to Extract just column headers from a table in Power BI?

lbendlin — Fri, 07 Jun 2024 02:00:51 GMT

The data structure is the opposite of what Power BI likes. Your requirement cannot be fulfilled in DAX as there is no way in DAX to dynamically address columns of a table.

You have to use Power Query to bring the data into a usable format by unpivoting the year columns. After that the inner join will be very easy.

Re: How to Extract just column headers from a table in Power BI?

Anonymous — Tue, 18 Jun 2024 06:25:27 GMT

Thanks for the reply from lbendlin , please allow me to complement the solutions you offer and offer additional insights:

HI,glyn1997
May I ask whether the post from lbendlin solves your problem? If it does, please accept his post as the solution.

Here are my additions to how to get column names:

First I have created the following table and the column names and data are the data you have given:

1.The first is the method lbendlin offers:
First select all the columns in the powerquery and then perform the reversal operation:

Next, use the "append query" for the two tables:

Finally, perform a deduplication operation in m language:

distinct1=Table.Distinct(Source),

2.Another method:

First you need to create two new queries with refrence and then in the two new tables, execute the following m language:

let Source = new, names=Table.ColumnNames(Source), output1=Table.FromList(names,Splitter.SplitByNothing(),{"column name"}) in output1

let Source = old, names1=Table.ColumnNames(Source), output2=Table.FromList(names1,Splitter.SplitByNothing(),{"column name"}) in output2

Then click New Blank Query and enter the following m language in Advanced Editor:

let combinee=Table.Combine({#"new (2)",#"old (2)"}), distinct1=Table.Distinct(combinee) in distinct1

3.Here's my final result, which I hope meets your requirements.

4.Here is the relevant documentation:

Table.ColumnNames - PowerQuery M | Microsoft Learn

Table.FromList - PowerQuery M | Microsoft Learn

Table.Distinct - PowerQuery M | Microsoft Learn

Please find the attached pbix relevant to the case.

Best Regards,

Leroy Lu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.