Forum Discussion
Power Query - Get data from another table if condition is met
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
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?
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