Can't display the data because Power BI can't determine the relationship between two or more fields?

[Anonymous]

Hi,

I'm receiving the "Can't display the data because Power BI can't determine the relationship between two or more fields." error message and I do not understand why. I have searched the net for help but can't find a post that answers my question.

It may well be a straight modelling restriction but best to check.

I have two datasources (Sheet 1 and Sheet 2). Both are Excel files. They are as below.

The tables above are only snippets of the full dataset. Both tables have duplicates and you will also notice NULLs in Sheet 1 for the 'Delivery' field. Sheet 2 may have them too.

The end result is for fields, from both tables, to be present in a 'table' visual.

For example,

Description (Sheet 1)     GI Date (Sheet 1)           Delivery (Sheet 1)       Item (Sheet 1)     Group (Sheet 2)

car                                  05/06/2017                   00000000                    0                          whatever the Group is

bus                                 27/05/2017                   81088293                    200                      whatever the matching Group is

You can get my drift.

The fields that are the JOIN is a concatenation of Delivery+Item (from Sheet 1) and Document+Item (from Sheet 2). At present it is a Many-to-Many relationship. If the Delivery or Document values are NULL I replace it with '00000000'.

Of course if there are many NULLs in Sheet 1 they will have the Key of 00000000+0 then when joining the to Sheet 2. If Sheet 2 have concatenated keys of 00000000+0 then HOW IS IT KNOWN WHICH 'GROUP' TO RETURN FROM SHEET 2?  I don't think it can.

To attempt to cater for the Many-to-Many join I have tried the following:

1) a) Concatenate the Delivery and Item fields for Sheet 1.

    b) Copy the Sheet 1 table (this will be a bridging table).

    c) Remove all columns but keep the concatenated key column.

    d) Remove duplicates from the concenated key column.

    e) Repeat a-d (above) for Sheet 2 too (but for Sheet 2 we are concatenating Document + Item).

    f) Join Sheet 1 to the Sheet 1 bridging table, join Sheet 1 bridging table to Sheet 2 bridging table, join Sheet 2 bridging table to Sheet 2.

2) a) Extract the Delivery column from Sheet 1 into table A.

    b) Extract the Document column from Sheet 2 into table B.

    c) Append (union) both table A and B into a new query (table).

    d) Remove the duplicates from the newly appended table.

    e) Repeat steps a-d for the Item column in Sheet 1 and Sheet 2.

    f) Join the Delivery column from Sheet 1 to the bridging table (created in step c).

    g) Join the Document column from Sheet 2 to the bridging table (created in step c).

    h) Join the Item column from Sheet 1 and Sheet 2 to the Item bridging table (created in step e).

Both these attempts produced the "Can't display the data because Power BI can't determine the relationship between two or more fields" error message.

As the first four columns in the final table visual are from Sheet 1 there is no issue at this point but as soon as I bring in the 'Group' column (which is from Sheet 2) I get the error.

Please someone advise:

1) exactly what the problem is?

2) is there a way of acheiving what I am attempting?

3) any good links to thoroughly explain a solution to my issue?

Your help will be much appreciated. Thanks.