Replace a value on a column base on multiple criteria in different columns

[ousslaraichi]

Hi All,
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	N
6	A	Intl	N
7	C	Intl	N
8	C	Intl	N

What I need if a transformation that will change my Boolean as follows:
If a GroupId with a "Intl" Indicator has at least one row with a Boolean at Y, all the rows 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	N
6	A	Intl	Y
7	C	Intl	N
8	C	Intl	N

Any help would be greatly appreciated.

[Jimmy801]

Hello

I would suggest you to use a combination of List.AnyTrue, Table.SelectRows and Table.TransformColumns

see my example here: I have decided to output a real logical instead Y or N. In case apply another TransformColumns

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXIEYs+8khwgFakUqxOtZARkOQGxS34ukPQDixljU2iCTdAUi24zbArNgSxnhCBEpQWGYCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Id = _t, GroupId = _t, Indicator = _t, Boolean = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Id", Int64.Type}, {"GroupId", type text}, {"Indicator", type text}, {"Boolean", type text}}),
    AddColumn = Table.AddColumn
        (
        #"Changed Type", 
        "New Boolean", 
        (newcolumn) => 
            List.AnyTrue
            (

            Table.TransformColumns
                (
                Table.SelectRows
                    (
                    #"Changed Type", 
                    each ([GroupId]= newcolumn[GroupId]) and ([Indicator]= "Intl")
                    ),
                {"Boolean", each if _ = "Y" then true else false}
            )[Boolean]
            )
        )

in
    AddColumn

 Have fun

Jimmy

[Anonymous]

Below is a one query solution. Hope it helps,

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXIEYs+8khwgFakUqxOtZARkOQGxS34ukPQDixmjKoQImmATNMWi2wybQnMgyxld0AJDMBYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Id = _t, GroupId = _t, Indicator = _t, Boolean = _t]),
    #"Filtered Rows" = Table.SelectRows(Source, each ([Indicator] = "Intl") and ([Boolean] = "Y")),
    #"Grouped Rows" = Table.Group(#"Filtered Rows", {"GroupId"}, {{"Count", each Table.RowCount(_), type number}}),
    #"Merged Queries" = Table.NestedJoin(Source, {"GroupId"}, #"Grouped Rows", {"GroupId"}, "Grouped Rows", JoinKind.LeftOuter),
    #"Added Custom" = Table.AddColumn(#"Merged Queries", "NewBoolean", each if Table.IsEmpty([Grouped Rows]) then [Boolean] else "Y"),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Boolean", "Grouped Rows"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"NewBoolean", "Boolean"}})
in
    #"Renamed Columns"

[v-juanli-msft · Accepted solution]

Hi @ousslaraichi 

As suggested by supergimi, i make steps below:

1. right-click on the query, make a duplicate to get "query 1",

2. in "Query 1", add a custom column, then remove other columns, only keep the "Custom" column, remove blank rows for this column

3.Merge queries, then expand this column

4. add a conditional column

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-juanli-msft]

Hi @ousslaraichi 

Create a calculated column

Column =
VAR flag =
    CALCULATE (
        COUNT ( 'Table'[GroupId] ),
        FILTER (
            'Table',
            'Table'[GroupId] = EARLIER ( 'Table'[GroupId] )
                && 'Table'[Indicator] = "Intl"
                && 'Table'[Boolean] = "Y"
        )
    )
RETURN
    IF ( flag = 1, "Y", [Boolean] )

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi,

calling your query A,

you can create two new queries, B and C referencing to A.

Then you can filter B set Indicator = "Intl" and Boolean = Y

Then select GroupId column in B and remove other columns

Remove duplicates from GroupId in B

Select C and merge query B  by GroupId Column with left join condition

Expand the new B column in the C query 

Add conditional column, named NewBoolean, setting this condition: if Indicator = "Intl" and B.GroupId is not null then Y else Boolean

Regards

Lorenzo