cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Adham
Helper III
Helper III

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

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:

 

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

 

Table 2:

 

IDDateValue
11/10/2020  12
22/10/2020  23
23/10/2020  34
24/10/2020  45
67/10/2020  78
68/10/2020  89
69/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:

 

IDStatusValueNew Column
1Closed  3333
2Open  4412
3Long Term  5555
4Open  66null
5Closed  7777
6Open

  88

78
7Long Term  9999

 

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.

1 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 

SU18_powerbi_badge

View solution in original post

4 REPLIES 4
AlB
Super User
Super User

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 

SU18_powerbi_badge

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?

@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 

SU18_powerbi_badge

Works perfectly! Thanks alot @AlB  really appreciate it!

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Dev Camp Session 30

Ted's Dev Camp - January 26, 2023

This session will provide guidance and teach campers the skills required to build Power BI reports that support multiple languages.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.