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

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.

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.