cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

Relationship between Column Header and another table

I have a table of information about my column headers and would like to connected them. Is it possible to create a relationship between column headers and another table. 

For example, like this (https://community.tableau.com/thread/152991) in Tableau.

 

Thank you!

2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
Community Champion
Community Champion

Re: Relationship between Column Header and another table

Looks to me like this is only possible when you transpose TableA.

 

Below the code how it looks like in Query Editor - Advanced Editor:

 

let
    Source = TableA,
    #"Demoted Headers" = Table.DemoteHeaders(Source),
    #"Transposed Table" = Table.Transpose(#"Demoted Headers")
in
    #"Transposed Table"

 

Compilation picture of the resulting table and the relationships:

 

Relationship between Column Header and another table 2.png

 

Going offline now as it's almost midnight over here.

Specializing in Power Query Formula Language (M)

View solution in original post

Highlighted
Community Champion
Community Champion

Re: Relationship between Column Header and another table

Maybe there is a workaround to achieve what you eventually require.

I mean: creating a relationship can never be the end goal.

Proabbly you want to end up with some visual(s) with information from both tables.

Specializing in Power Query Formula Language (M)

View solution in original post

8 REPLIES 8
Highlighted
Community Champion
Community Champion

Re: Relationship between Column Header and another table

Maybe you can use Table.ColumnNames in Power Query.

 

For more specific help, please provide more specific information about your data and your requirements.

If the required information is in the link you provided, please be more specific where exactly in the link the information can be found.

Specializing in Power Query Formula Language (M)
Highlighted
Helper I
Helper I

Re: Relationship between Column Header and another table

Thank you for your reply @MarcelBeug.

My issue is almost identical to that of Kirstin's in this (https://community.tableau.com/thread/152991) Tableau forum. 

Like Kirstin, I have columns in a table, say A, called f1, f2, ..., f100 (these are Table A's column headers). I also have another table, say B, with corresponding specifics, such as fi's (for i from 1-100) exact question and question type. However, in Table B, I also have a column, say b1, containing each question's id, the fis from above. I'd like to create a relationship between Table A's column headers and column b1 from Table B. Is this possible? How can I do this? Can I do this without downloading Power Query?

Highlighted
Community Champion
Community Champion

Re: Relationship between Column Header and another table

Anything is possible with Power Query, which is the "Query Editor" part of Power BI, so it comes with the product.

 

I painted a picture how I understand your input would look like. Table A to the left and table B to the right.

Now you may paint a picture how the result should look like.

 

Relationship between Column Header and another table.png

Specializing in Power Query Formula Language (M)
Highlighted
Helper I
Helper I

Re: Relationship between Column Header and another table

@MarcelBeug, I'm sorry for the confusion!

Here is what I envisioned Table A (left) and Table B (right) to look like.

Now, I'd like to create a relationship between the column header of Table A and column b1.

 

Column Header Mapping.PNG

Highlighted
Community Champion
Community Champion

Re: Relationship between Column Header and another table

Looks to me like this is only possible when you transpose TableA.

 

Below the code how it looks like in Query Editor - Advanced Editor:

 

let
    Source = TableA,
    #"Demoted Headers" = Table.DemoteHeaders(Source),
    #"Transposed Table" = Table.Transpose(#"Demoted Headers")
in
    #"Transposed Table"

 

Compilation picture of the resulting table and the relationships:

 

Relationship between Column Header and another table 2.png

 

Going offline now as it's almost midnight over here.

Specializing in Power Query Formula Language (M)

View solution in original post

Highlighted
Helper I
Helper I

Re: Relationship between Column Header and another table

Unfortunately, transposing Table A is not an option for me. 

Thank you, @MarcelBeug, for your help!

Highlighted
Community Champion
Community Champion

Re: Relationship between Column Header and another table

Maybe there is a workaround to achieve what you eventually require.

I mean: creating a relationship can never be the end goal.

Proabbly you want to end up with some visual(s) with information from both tables.

Specializing in Power Query Formula Language (M)

View solution in original post

Helper I
Helper I

Re: Relationship between Column Header and another table

Perhaps. Thank you!

Helpful resources

Announcements
Meet the 2020 Season 2 Power BI Super Users!

Meet the 2020 Season 2 Power BI Super Users!

Find out who's part of the program this season, and welcome the new Super Users.

August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

July 2020 Community Highlights

July 2020 Community Highlights

Learn about the exciting things that happened in July.

Featured Data Story of The Month

Featured Data Story of The Month

All Data Stories Gallery contributions are reviewed for each month. We select a contribution and feature the community member the following month.

Power BI Dev Camp - Developing with .NET Core

Power BI Dev Camp - Developing with .NET Core

Learn how to develop custom web applications for Power BI using .NET Core 3.1 and .NET 5.

Top Solution Authors
Top Kudoed Authors