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

Relate Horizontal Columns with List

Hi all,

 

I have a list of time-stepped data and I'm trying to create a slicer so that I can filter the data without having to drag and drop it each time. I can't think of a way to create a relationship without having to create a singular relationship for each field? Example Below:

 

Table 1

DayBasin ABasin BBasin C

3/1/20

146
3/2/20324
3/3/20543
3/4/20216

 

 

Table 2

Basins
Basin A
Basin B
Basin C
1 ACCEPTED SOLUTION
Ashish_Mathur
Super User
Super User

Hi,

In the Query Editor, right click on the first column on Table1 and select "Unpivot other columns".  Now create a relationship (Many to One and Single) between Table2 and the Attribute column of Table1.

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

5 REPLIES 5
Ashish_Mathur
Super User
Super User

Hi,

In the Query Editor, right click on the first column on Table1 and select "Unpivot other columns".  Now create a relationship (Many to One and Single) between Table2 and the Attribute column of Table1.

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

worked great, I had over 20 basins, but was able to select them all and unpivot at the same time, thank you so much!

You are welcome.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
bchager6
Continued Contributor
Continued Contributor

I couldn't have said it better myself!

bchager6
Continued Contributor
Continued Contributor

I would denormalize Table 1 if possible (unpivot the Basin A, Basin B, and Basin C columns), then connect the two tables via a 1:n relationship on Basin.

Denormalized Table 1

bchager6_0-1631132201160.png

 




Helpful resources

Announcements
June 2022 update 768X460.jpg

Check it out!

Click here to read more about the June 2022 updates!

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

June 20 episode 7 with aka link 768x460.jpg

The Power BI Community Show

Join us on June 20 at 11 am PDT when Kim Manis shares the latest on Azure Synapse analytics, the Microsoft Intelligent Data Platform, and notable Power BI Updates from Microsoft Build 2022.

Top Solution Authors