Import table based on another table column value

[Anonymous]

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

[v-alq-msft · Accepted solution]

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.

[AllisonKennedy]

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. 

[Anonymous]

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.