Adding data from column from unrelated table

[DanielB_NL]

Hi all! 

This is the situation:

• Our company is working with an ERP-system, based on an MSSQL-database
• I connect with Power BI to the database and normally this works fine: I can find the tables, create the relationships and get the desired data
• In this particular situation the starting point was that I wanted to recreate a form from our ERP-system and thought it was simple by putting the colums from the table stock_purchase_advice_article_purchase_receipt into a visual, but I'm missing an important column.
• I can see that in the ERP-system the colum is added from another table purchase_receipt_line with the help of this SQL-query 
  
  select min(prl.sequence_nr)
  from purchase_receipt_line prl
  where prl.purchase_receipt_nr = t1.purchase_receipt_nr
  and prl.article_code = t1.article_code
  
  So, if I'm right: this is a join based on the purchase_receipt_nr and the article_code? I've tried to make a relationship based on these two columns (used the CONCATENATE-function to merge two columns, so I could trick power BI to make a multi-column relationship), but I still get a many-to-many relationship with unexpected results, so this isn't working.
• My question, how do I convert the SQL-query in such a way I can add a column sequence_nr to the stock_purchase_advice_article_purchase_receipt table in a similar way?

Example of how the table in the ERP-system looks like: in yellow the column which is added with the SQL-statement.

I hope someone can help me out! Thanks in advance,

[mohammedadnant · Accepted solution]

Hi @DanielB_NL 

One workaround is that creating a bridge table,

1. extract the purchase_receipt_nr and article_code from both the tables

2. append these 2 new tables add a new column to concatenate 2 columns, and remove duplicates --> now this is a dimension table with 3 columns (purchase_receipt_nr, article_code & concatenate of these 2)

3. do the concatenate in both the detailed tables

4. make the relationship from the dimension table to both the detailed tables with concatenate column

5. take the purchase_receipt_nr and article_code from the dimension and others as regular measures...

hope this will help.. 

Thanks & Regards,

Mohammed Adnan

Learn Power BI: https://www.youtube.com/c/taik18

[DanielB_NL]

Thanks Mohammed,
Can you please elaborate how to do this and how to extract the purchase_receipt_nr and article_code from both the tables? 

For your information: I'm connecting to the tables in DirectQuery mode: I don't know if this relevant for your solution? Kind regards,

[mohammedadnant · Accepted solution]

Hi @DanielB_NL 

One workaround is that creating a bridge table,

1. extract the purchase_receipt_nr and article_code from both the tables

2. append these 2 new tables add a new column to concatenate 2 columns, and remove duplicates --> now this is a dimension table with 3 columns (purchase_receipt_nr, article_code & concatenate of these 2)

3. do the concatenate in both the detailed tables

4. make the relationship from the dimension table to both the detailed tables with concatenate column

5. take the purchase_receipt_nr and article_code from the dimension and others as regular measures...

hope this will help.. 

Thanks & Regards,

Mohammed Adnan

Learn Power BI: https://www.youtube.com/c/taik18

[DanielB_NL · Accepted solution]

Hi @mohammedadnant,

It was a step in the right direction.  In the end it ended up in a many-to-many relation. I used your suggestion to create a dimension table, but added the sequence no. column to this table, created the concatenated column based on purchase_receipt_nr and article_code and removed duplicates based on the concatenated column. In practice, the chance that on one Purchase Receipt No. there are more of the same Article is very small, so the 'damage' done by removing the duplicates is close to zero.

[mohammedadnant]

 Hi @DanielB_NL 

Thanks for your reply, in your case, you are right, sometimes it is needed,

could you please give thumbs up to my answer.

Thanks & Regards,

Mohammed Adnan