Search code examples
variablesgroup-bypowerbipowerquerym

Power Query - How to add a Conditional Column based on Variables

I have the following problem and I'm looking for solutions in Power Query i.e. not in DAX. This is my starting point:

Table Name: Reference Register

Credit Number Currency Credit
ABC123 USD 1,000,000
ABC123 EUR 2,000,000
ABC123 GBP 3,000,000
OPQ654 USD 3,500,000
OPQ654 EUR 4,000,000
OPQ654 GBP 5,000,000
XYZ555 USD 10,000,000
XYZ555 EUR 11,000,000
XYZ555 GBP 13,000,000
JKL900 USD 20,000,000
JKL900 EUR 19,000,000
JKL900 GBP 16,000,000

I want to add an new column which labels each row with either a "Pass" or Fail", as per the following conditions:

For every Credit Number,

if the Credit with Currency USD is null, then all entries for the same Credit Number should get a "Fail"

else if Credit with Currency USD is > null && the Credit with Currency USD is greater than the Credit with Currency EUR, then all entries for the same Credit Number should get a "Pass"

else if Credit with Currency USD is > null && the Credit with Currency USD is smaller than the Credit with Currency EUR, then all entries for the same Credit Number should get a "Fail"

else, all entries for the same Credit Number should get a "Pass"

I tried pretty much the above with the various if/else statements, but because I need to look up on a Credit Number Level i.e. it can be multiple rows to look over, I got stuck as I don't know how to best use variables (?), the group by function (?) or any other approach.

For the sample, the expected outcome would be:

Credit Number Currency Credit Pass/Fail
ABC123 USD 0 Fail
ABC123 EUR 2,000,000 Fail
ABC123 GBP 3,000,000 Fail
OPQ654 USD 3,500,000 Fail
OPQ654 EUR 4,000,000 Fail
OPQ654 GBP 5,000,000 Fail
XYZ555 USD 10,000,000 Fail
XYZ555 EUR 11,000,000 Fail
XYZ555 GBP 13,000,000 Fail
JKL900 USD 20,000,000 Pass
JKL900 EUR 19,000,000 Pass
JKL GBP 16,000,000 Pass

Any thoughts how I could best add a column with output based on comparisons at Credit Number level in Power Query? Much appreciated!

Solution

  • Try this in powerquery

    let  Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Grouped Rows" = Table.Group(Source, {"Credit Number"}, {{"data", each 
    let #"Pivoted Column" = Table.Pivot(_, List.Distinct(_[Currency]), "Currency", "Credit", List.Sum),
    #"Added Custom" = Table.AddColumn(#"Pivoted Column", "Custom", each if ([USD]=null or [USD]=0) then "Fail" else if [USD]>[EUR] then "Pass" else if [USD]<[EUR] then "Fail" else "Pass")
    in #"Added Custom"[Custom]{0}  }}),
#"Merged Queries" = Table.NestedJoin(Source, {"Credit Number"}, #"Grouped Rows", {"Credit Number"}, "xx", JoinKind.LeftOuter),
#"Expand" = Table.ExpandTableColumn(#"Merged Queries", "xx", {"data"}, {"Pass/Fail"})
in #"Expand"

    enter image description here