Forum Discussion
Import table based on another table column value
Hi,
I got the following 2 Tables, the first one which contains all the data, and the Second TABLE which contains only 5 columns, and ColX is present only in Table 2. To link the two tables, I need to create a concatenation of the first 4 columns Col1&Col2&Col3&Col4 of each table in order to make a unique key.
Now I need to load the data from Table 1 only when Col X of Table 2 has certain values, let say 4 or 0.
Which is the best way to load the data in PowerBI and how?
| Table 1 | Table 2 |
| Col 1 | Col 1 |
| Col 2 | Col 2 |
| Col 3 | Col 3 |
| Col 4 | Col 4 |
| . . . | Col X |
Thanks
Hi, Anonymous
Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.
Table1:
Table2:
You may add a new step with the following m codes for 'Table1'.
= Table.SelectRows( #"Changed Type", each let tab = Table.SelectRows(Table2,(x)=>x[Society]=[Society] and x[Year]=[Year] and x[Trim]=[Trim] and x[Scenario]=[Scenario]), val = try List.Sum(tab[Col X]) otherwise null in if val=4 or val=0 then true else false )Result:
\
Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
Do either TAble 1 or Table 2 have unique values?
You can do this all in Power Query - merge the columns to create a unique key and filter Table 2 by Column X (just use the filter arrows in Power Query). Then relate the two tables if unique values exist.https://excelwithallison.blogspot.com/2020/08/its-complicated-relationships-in-power_11.html
Otherwise we need more info on the dataset.
Table 1 has all the data, while Table 2 has the combination of the four columns (Society/Year/Trim/Scenarios and this combination will serve as a key between the two tables) and one additional column which serves as a qualifier of data, so if the value is 4 the data for that specific combination is certified, so I should import the data for that combination from Table 1.
Table 2 is filled only when the value for a specific combination is 4, (so if any combination is non-certified it will be empty), after the combination has been certified, so has value 4, it can be changed (ex. in 1 -not certified) and I don't have to import the data from Table 1.
Hi, Anonymous
Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.
Table1:
Table2:
You may add a new step with the following m codes for 'Table1'.
= Table.SelectRows( #"Changed Type", each let tab = Table.SelectRows(Table2,(x)=>x[Society]=[Society] and x[Year]=[Year] and x[Trim]=[Trim] and x[Scenario]=[Scenario]), val = try List.Sum(tab[Col X]) otherwise null in if val=4 or val=0 then true else false )Result:
\
Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
