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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
s-turn
Resolver I
Resolver I

Many-to-many relationship when values are DEFINITELY unique!

Hello,

 

I've seen a few threads about this, and the root cause was always that there were blank values in the lookup table, or that Remove Duplicates in Power Query had not removed duplicates with different upper/lowercasing.  I have checked and made sure that this is all taken care of, so I don't think this is the problem here.

 

It affects three look-up tables, TributesFromPayments, SourcesFromPayments and FundsFromPayments.  All Payments should have a Source and a Fund, and some Payments also have a Tribute.  I would expect there to be one-to-many relationships between these tables and the fact table PaymentsTrimmed.

 

The common link seems to be that all three affected look-up tables are generated via Power Query from a fact table.  It works like this: 

1) I import data to the query AllPaymentsPlus, which is basically all transactions on the system.  I then create four queries from it:

--PaymentsTrimmed, which is a tidier, more usable version of AllPaymentsPlus.  It retains lookup IDs for Tributes, Sources and Funds (which are used to join it to the following look-up tables in the data model), but not the descriptions or any other metadata.

--TributesFromPayments, which takes (and dedupes) the Tribute ID and other descriptive fields about Tributes.

--SourcesFromPayments, which takes (and dedupes) the Source Code and other descriptive fields about Sources.

--FundsFromPayments, which takes (and dedupes) the Fund Code and other descriptive fields about Funds.

 

Here you can see how the data model insists on these being many-to-many relationships:

Rships Tribute Source Fund.PNG

I have done all the checks I can think of to make sure there are not duplicates in these tables, including exporting the data to Excel and checking there.  I wondered whether SourcesFromPayments was being affected somehow by the other tables it is related to, but TributesFromPayments and FundsFromPayments are not related to any other tables and they are affected in the same way.

Any suggestions very gladly received!

1 ACCEPTED SOLUTION

Hi @amitchandak , I'd originally posted a link to the .pbix here, but having spent a bit more time on this problem tonight I have figured out that the problem was with white space.  I used the Trim function in Power Query, then deduped again, and it seems to be fixed! 

Silly me for making a post about how the values were "DEFINITELY unique"... I should have known Power BI was more likely to be right than I was!

View solution in original post

2 REPLIES 2
amitchandak
Super User
Super User

@s-turn , if there is no duplicate and blank then 1-M should work. Is it possible to share a pbix.

 

Can you share a sample pbix after removing sensitive data.

Hi @amitchandak , I'd originally posted a link to the .pbix here, but having spent a bit more time on this problem tonight I have figured out that the problem was with white space.  I used the Trim function in Power Query, then deduped again, and it seems to be fixed! 

Silly me for making a post about how the values were "DEFINITELY unique"... I should have known Power BI was more likely to be right than I was!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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