CODEWITHSUNDEEP
powerbipowerquery
MOL
MOL

Reputation: 11

Power BI Query Editor: Replace a value on a column base on multiple criteria in different columns

I have a table that looks like this:

Id GroupId Indicator Boolean 1 A Intl Y 2 B Dom N 3 A Intl N 4 A Intl N 5 B Dom Y 6 A Intl N 7 C Intl N 8 C Intl N

What I need if a transformation that will change my Boolean as follow: If a GroupId with a "Intl" Indicator has at least one row with a Boolean at Y, all the row with the same GroupId get the Y.

My final table should look like this below, where Ids 3,4 and 6 had their Boolean changed to Y.

Id GroupId Indicator Boolean 1 A Intl Y 2 B Dom N 3 A Intl Y 4 A Intl Y 5 B Dom Y 6 A Intl Y 7 C Intl N 8 C Intl N

Any help would be greatly appreciated.

Upvotes: 0

Views: 233

Answers (1)

Aleksei Zhigulin
Aleksei Zhigulin

Reputation: 1634

This code should work:

let
    Source = Excel.CurrentWorkbook(){[Name="Table"]}[Content],
    group = Table.Group(Source, {"GroupId"}, {"temp", each let x = List.Contains([Boolean],"Y")
                                                           in if x then Table.ReplaceValue(_,each [Indicator] = "Intl",0,(a,b,c)=>if b then "Y" else a,{"Boolean"}) else _}),
    combine = Table.Sort(Table.Combine(group[temp]),{"Id", 0})
in
    combine

enter image description here

Upvotes: 1

Related Questions