Hoping someone can point me in the right direction. Looking for video or document as I am still in the learning phase.
I have several tables that have a column that is order #. If I try to connect them is gives me Many to many relationship. This is because there are several products listed per order number.(So several rows has a product and same order#) I would like to create a lookup table that also updates any new order numbers and then removed duplicates so that when connecting/filter these three tables, it will be Many to one?
Each table has the same Order # so I only need to pull from one to create the lookup table.
I tried to merge the tables But the tables are very big and over 400000 rows on the main file.
I have also tried to used Distinct to pull Order # into a new table. That worked Kind of. I can see the values in the data view. But in Report it will not show any data.
Also in the model I was able to only link one of the Data tables to the lookup table I created.
HI @dnw1970 , while waiting for other members to jump in, I suggest using the forum search to discover if your query has been asked before by another member. Thanks!
Thanks, I have continued my search. I can get the values into another table using DISTINCT, But it still does not work. I am amazed that is this so hard. I may just end up creating a table in report and then exporting into an excel lookup table.
Thanks for posting this question. See quite simple and straightforward as we encounter such issue many times. You can have 2 options here:
Create a lookup table as you are trying. There can be many ways and the best way is to create lookup table in Power Query, but I have created in DAX to simulate your experience. I have created lookup table using DISTINCT,
The other method you can use is to create a composite column by combining sales ordered and product id and then you might get unique values.
By implementing first method, I have simulated your scenario and it is working fine. Please let me know in case anything is not clear to you.
Thank you @hafizsultan! “Create Lookup table in Power Query” was the tip I needed. I watched a You tube video after that to used Reference to create the table. I put it in my Model and it works!!
