Create reference between 2 tables where the rows in table 1 match the column headers in table 2

[Jason_Apt]

Hello - 

I am newer to PowerBI and appreciate any suggestions that come from my question.

I have an excel spreadsheet that is being used to keep track of 40 yard dash times for players on a team.  Column A has the list of players and Columns B...n are the times for each timed run per user.  

I am trying to create a simple dashboard that consists of 2 visualizations.  The first would be a table of the player names and the second would be a line graph with Time (y) and runs (x).  The table would be used to filter the line graph to a single runner.

My problem with the data is that I can only get a dot on the graph in a virtical line for each players run and not a horizontal line.  The table filtering works.

I decided to transpose the spreadhseet and put the Run1 in column 1 and then the other column headers are the player names.  This allows the graph to have a horizontal line but I cannot figure out a way to create the chart for player names and filter.  

I am looking for either a more efficient way to create this...or a way to create a table just for player names and create a reference to the data.

Thanks in advance!

Jason

[amitchandak · Accepted solution]

Better you Unpivot the tables having the row data in the column and then try

https://radacad.com/pivot-and-unpivot-with-power-bi

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...
https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p...
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601

[Icey · Accepted solution]

Hi @Jason_Apt ,

Can this meet your requirements?

Steps:

1. In Power Query Editor, select "PlayerName" column and then "UnPivot other columns".

2. Close and Apply.

3. Create a line chart.

4. Create PlayerName slicer and Time Slicer.

For more details, please check the attached PBIX file.

Best Regards,

Icey

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

[Jason_Apt]

@Icey Thank you for this support.  I had run past unpivot while researching but didn't understand the concept at the time for whatever reason.  I was then going through a Pluralsight course and had gotten to the point of seeing it in action and your reply came in so it all clicked and I have it working!

[amitchandak · Accepted solution]

Better you Unpivot the tables having the row data in the column and then try

https://radacad.com/pivot-and-unpivot-with-power-bi

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...
https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p...
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601

[Jason_Apt]

@amitchandak Thank you for this.  It also helps in the solution.  I saw the other post first and marked as solution so I will also mark this one.