Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.