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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Power BI Header Change

Power BI Header Update

Read about the exciting updates for the Power BI forum.