"Many-to-Many" Error when mapping relationship between tables

[michellepace]

Hi there,

I'm really stumped on this one. I have a Financial Categories table where each AccNumber is the primary key (718 distinct, 718 unique). I have a second table Financial Data that also has the AccNumber column. Here the values are not unique (718 distinct, 0 unique). When I try to map these tables on the AccNumber column, I get a "many to many" error. 

From each of these two tables I copied the AccNumber column into excel. I then re-imported these tables and I could map the relationship between the two without any problem at all (see yellow).

What on early is going on? Why can't I map the relationship then betweel Financial Categories and Financial Data on their common AccNumber column (where AccNumber is unique in Financial Categories). Really confused. 

Please help!

Regards

Michelle

[DataZoe]

@michellepace Building on what @Greg_Deckler has said, while they are numeric they are coming in as a text format. You can change this in the "Transform Data" and on the column header change them to a whole number data type:

With them both numeric, you can try the relationship again. Also, I sometimes get this error when there are blanks, so remove empty doesn't hurt if you aren't expecting blanks!

[michellepace · Accepted solution]

Hi @DataZoe . Thank you for your suggestions. I worked through all the steps listed in Query Settings. I found the relationship acutally worked up until the step of "Unpivot Other Columns." I redid this step using "Unpivot only selected columns"... for whatever reasons (which is quite beyond me)... the relationship now works. If someone else gets a similar problem... I'm not quite sure if this solution will be repeatable. Anyhow. There it was. Thanks for your suggestions though, I've made a mental note in case this happens to me again.

Cheers! Michelle

[DataZoe]

@michellepace Wow that's interesting and good to know! Glad you got it working 🙂

[Greg_Deckler]

@michellepace - Sometimes "unique" is different between Power Query and DAX (often involves upper and lower case characters). That may be the issue. Hard to say though.

You could try creating a bridge table like this:

Table = DISTINCT(UNION(SELECTCOLUMNS('Table1',"Column",[Column1]),SELECTCOLUMNS('Table2',"Column",[Column2])))

[michellepace]

Hi @Greg_Deckler . Thank you for your reply. I am matching on a numeric field. There is no upper and lower case. Also, it works as expected if I export the data to excel and reimport from there. Surely Power Bi can't be so fickle? I must be doing something wrong?

[Greg_Deckler]

@michellepace - Well, there are digits in there but those fields are not true numeric fields. They are text fields that contain numbers. Try doing Clean and Trim operations on the columns in both tables. There may be some spaces at the beginning/end and other such things that are causing issues.