Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
GabrielFLima
Frequent Visitor

Create a relationship between tables where any of 2 specific columns values may match the original

I have a report that basically drills down a table based on the selection (you select a person and all the stuff 'owned' by this person which are in other tables is shown). For this, I had some one-to-many relationships set between the person's name in the original table and the 'owner's' name in the tables which have the content.

 

It worked just fine, but recently there's been a change and the 'stuff' can belong to 1 or 2 people, which means that I can no longer look at only one field to determine if that row interests me or not.

 

Basically I'd like PBI to retrieve all rows from those auxiliary tables where ANY of the 2 'owner' columns contain the name of the selection.

 

I've been looking around for quite some while and haven't found someone with a similar question, so I hope someone can give me a little hand here with what I could try since I'm kind of stuck 😛

 

Thank you all in advance!

 

Edit: Adding some sample data so my problem can be better visualized:

 

I have a Ranking table which contains these key fields;

 

Ranking

Name

Points

Person 1

100

Person 2

80

Person 3

60

...

...

 

And a few "auxiliary" tables with the documents sent by each employee. These tables are all modeled like this:

 

Auxiliary

Doc. Owner 1Doc. Owner 2Doc. NamePoints
Person 1 Doc 150
Person 3Person 1Doc 230
Person 2 Doc 390
... ...

...

 

As I said, I already have a relationship set between these tables from the time there could only be 1 owner per document (also there are many Auxiliary tables):

 

Ranking               Auxiliary

 Name       1:*     Doc. Owner 1

 

The Ranking table is what is shown to the user, and by hovering over each person's row the amount of documents sent by that person is shown as a tooltip.

 

The problem is that when there's more than one owner, I get forced to pick which auxiliary column I'll be using for the relationship, and because of this the value shown will be wrong. For example, for the values given, Person 1 should have 2 docs, but the report will only show 1.

 

Hope things are clearer now.

1 ACCEPTED SOLUTION

Something like this:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCkgtKs7PUzBU0lFSAGKX/GQw29RAKVYHLmsMFEFSCFJkBKSNURQZIRkB0mAJlI0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Doc. Owner 1" = _t, #"Doc. Owner 2" = _t, #"Doc. Name" = _t, Points = _t]),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Doc. Name", "Points"}, "Attribute", "Doc Owner"),
    #"Removed Other Columns" = Table.SelectColumns(#"Unpivoted Other Columns",{"Doc. Name", "Points", "Doc Owner"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Other Columns", each ([Doc Owner] <> " "))
in
    #"Filtered Rows"

How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done".

View solution in original post

9 REPLIES 9
lbendlin
Super User
Super User

This is easy to do with a custom column generator function.

 

Please provide sample data that covers your issue or question completely.
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
Please show the expected outcome based on the sample data you provided.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

Thanks a lot!

I edited the post to include sample data (even though my tables' formatting is a little off), did it get better?

Can the format of your auxiliary table be changed?  Generally you want to unpivot such tables before using them in a data model.

Yes it can, it's a Sharepoint list where the documents are added and the points are given by some flows, and I have full access to it since I'm the one developing it.

 

Now what exactly do you mean by 'unpivot' the tables? English isn't my first language, so the only thing I relate it to are Excel's pivoted tables.

You would go from 

 

Doc. Owner 1 Doc. Owner 2 Doc. Name Points
Person 1   Doc 1 50
Person 3 Person 1 Doc 2 30
Person 2   Doc 3 90

 

to 

 

Doc. Owner Doc. Name Points
Person 1 Doc 1 50
Person 3 Doc 2 30
Person 1 Doc 2 30
Person 2 Doc 3 90

Ok I can see this working being formatted like this, what should I look into to make this transformation directly on Power Query?

Unfortunately I can't make this kind of change directly on Sharepoint since it would create some other problems, specially for consulting such documents in the future.

 

I did some research on pivotting/unpivotting tables in Power Query but still haven't found a similar result...

Something like this:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCkgtKs7PUzBU0lFSAGKX/GQw29RAKVYHLmsMFEFSCFJkBKSNURQZIRkB0mAJlI0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Doc. Owner 1" = _t, #"Doc. Owner 2" = _t, #"Doc. Name" = _t, Points = _t]),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Doc. Name", "Points"}, "Attribute", "Doc Owner"),
    #"Removed Other Columns" = Table.SelectColumns(#"Unpivoted Other Columns",{"Doc. Name", "Points", "Doc Owner"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Other Columns", each ([Doc Owner] <> " "))
in
    #"Filtered Rows"

How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done".

My man you have no idea how much you helped me, I can't thank you enough!! 😄

 

Btw I found out that if instead of "Unpivoting other columns" you choose to unpivot only the selected columns, it doesn't create rows with empty "owners", and the "filtering" step is no longer needed.

oh really?  I need to try that. Cheers!

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.