cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mattmalone
Frequent Visitor

Many-To-One Relationship not being recognised

Hi all,

 

I originally posted the below into an older thread - https://community.powerbi.com/t5/Desktop/Forced-to-use-many-to-many-relationship/m-p/789053#M379986 so apologies if posting this again upsets anyone.

 

The issue - I cannot create a one-to-many relationship between my Product table (which has a Primary Key field - SKU) and my Sales Transaction Table. 

 

No doubt I'm going something wrong as today is the first day that I'm really working with Power Query.

 

  • I've looked to filter out Null values in my Product List.

    = Table.SelectRows(#"Removed Columns", each [StockCode] <> null and [StockCode] <> "")

    I also created a calculated field to run an If, Then to see if anything came up positive for a null value. No positive results.

  • I've run another query to see if there are any duplicate values by Transform -> Group By ->Stockcode Count. No values greater than 1.

  • My only other guess (as I'm new to Power Query) is if there is an issue with a particular operator (@ being used in a SKU, for example) and if there are particular operators that may impact on being able to create a many-to-one relationship.

  • The transaction Table will have duplicate product codes and have duplicate order numbers due to the fact that one sale will have multiple lines and can have the same SKU appear against that transaction twice. This is obviously the many side of my relationship and I couldn't think why this would impact on anything. I added an Index in the table just to see if that would play a factor and it obviously didn't.

It's my first day getting really into Power, and I'm ssooo close yet sooo far!

1 ACCEPTED SOLUTION
mattmalone
Frequent Visitor

Hello all,

 

This particular query was difficult to resolve but got there in the end. Duplicates were not easy to find because they had a return carriage in them.

 

One SKU in a DB of 94,000 records.

 

Regards,

View solution in original post

3 REPLIES 3
mattmalone
Frequent Visitor

Hello all,

 

This particular query was difficult to resolve but got there in the end. Duplicates were not easy to find because they had a return carriage in them.

 

One SKU in a DB of 94,000 records.

 

Regards,

View solution in original post

v-juanli-msft
Community Support
Community Support

 Hi 

Is this problem sloved? 
If it is sloved, could you kindly accept it as a solution to close this case?
If not, please let me know.
 
Best Regards
Maggie
v-juanli-msft
Community Support
Community Support

Hi @mattmalone 

What is the error message when creating relationships unsuccessfully?

 

Besides the method you have tried, you could use "Edit queries"->Transform->Format->trim, clean.

 

You could use "Merge queries" to check if the [StockCode] in Product table match code column in Sales Transaction Table.

https://docs.microsoft.com/en-us/power-bi/desktop-shape-and-combine-data#combine-data

 

Here is a guide how to create relationship

https://docs.microsoft.com/en-us/power-bi/desktop-create-and-manage-relationships

 

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Get Ready for Power BI Dev Camp

Power BI Dev Camp - June 24th

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors