Power Query - Get data from another table if condition is met

[Adham]

Hello All,

I have been working on this all day with little luck, would really appreciate some support on this. I have two tables:

Table 1:

ID	Status	Value
1	Closed	33
2	Open	44
3	Long Term	55
4	Open	66
5	Closed	77
6	Open	88
7	Long Term	99

Table 2:

ID	Date	Value
1	1/10/2020	12
2	2/10/2020	23
2	3/10/2020	34
2	4/10/2020	45
6	7/10/2020	78
6	8/10/2020	89
6	9/10/2020	90

I want to create a conditional column in table 1 and do the following:

if the status of an id is closed or long term then use the value in table 1, else if the status is open then take the value from table 2 with the earliest date where the id is the same as that of table 1. If there are no entries in table 2 with the same id then it should be null

Here is how table 1 should look like:

ID	Status	Value	New Column
1	Closed	33	33
2	Open	44	12
3	Long Term	55	55
4	Open	66	null
5	Closed	77	77
6	Open	88	78
7	Long Term	99	99

This has to be done in power query as there is a very important python script i have to run on that table once i perform this step.

Thank you.

[AlB]

Hi @Adham 

You can create a custom column with the following code:

= if List.Contains({"Closed", "Long Term"}, [Status]) then [Value] else   Table.Sort(Table.SelectRows(Table2, (inner)=> inner[ID]=[ID]), {{"Date", Order.Ascending}})[Value]{0}

where Table2 is the name of your second table

Please mark the question solved when done and consider giving kudos if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[Adham]

Hello @AlB,

Thank you for your help! I tried the formula and it works well but it returns an error when it tries to read from table 2 and there are no rows with the same ID. The problem arises when i try to extract the value from the first row using this:

[Value]{0}

I think it raises an error as it tries to extract the value from the first row which doesnt exist. How can i make the formula work but return null values when there arent any rows with matching IDs in table 2?

[AlB · Accepted solution]

@Adham 

Just add the "try... otherwise" construct to catch the error and return null instead:

= Table.AddColumn(#"Changed Type", "Custom", each if List.Contains({"Closed", "Long Term"}, [Status]) then [Value] else try Table.Sort(Table.SelectRows(Table2, (inner)=> inner[ID]=[ID]), {{"Date", Order.Ascending}})[Value]{0} otherwise null)

Please mark the question solved when done and consider giving kudos if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[Adham]

Works perfectly! Thanks alot @AlB  really appreciate it!