Linking tables cascade

[Caseyjones]

Hello Community! 🙂

I'd like to solve a problem. I do hope you can help me. 🙂

I have two tables.
Table Sales with the columns: CustomerID, Date, Product, turnover.

Table Customer with the columns: CustomerID 1, CustomerID 2, CustomerID 3, Customer Name.

The CustomerID in the Sales table is to be searched for in the Customer table, first in column CustomerID 1. If it is not found, then in column CustomerID 2 or further in CustomerID 3.

At the end I would like to have a linked table with the columns: ID, Date, turnover, Customer Name

In Excel with a VLOOK UP this works without any problems.
In Power Query (Power BI) I have my difficulties.

I have already found a solution, namely by placing CustomerID 1 to 3 one below the other. So I have depivotized them. But isn't that a bit inefficient?

I hope you have a better suggestion. 🙂

Thanks a lot.

Stefan

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...