I want to set a flag in the query

[kai1109]

Hello. I'm using a translation, so it may be difficult to understand, but thank you for your help.

I would like to set a flag according to the conditions for the following sample data.

name	value	MasterCD
【20231218_XXX】sampleA	12	CD1BCD219CD32CD4-1CD51CD61
【20231218_XXX】sampleA	2	CD1BCD219CD32CD4-1CD52CD61
【231106_XXX】sampleB	12	CD1BCD219CD32CD4-1CD51CD61
【231106_XXX】sampleB	0	CD1BCD219CD32CD4-1CD52CD61

Conditions
- It is necessary to judge for each same name.
- If there is a value in MasterCD CD1BCD219CD32CD4-1CD52CD61, set a flag there.
- If CD1BCD219CD32CD4-1CD52CD61 is 0, set a flag in CD1BCD219CD32CD4-1CD51CD61.

In the case of the sample data, I would like to set a flag in the second and third lines.

[dufoq3]

Hi @kai1109, different approach here:

Result

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WetwwwcjAyNjQyNAiPiIi4nHDxOLE3IKcVEclHSVDIyDh7GLo5OxiZGjp7GJs5Oxiomvo7GIKxGaGSrE6+PXj1G6Eot3Y0NDADEWzEwmWY9dtQMDqWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [name = _t, value = _t, MasterCD = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"name", type text}, {"value", Int64.Type}, {"MasterCD", type text}}),
    GroupedRows = Table.Group(ChangedType, {"name"}, {{"All", each
        Table.AddColumn(_, "Flag", (x)=>
            if x[MasterCD] = "CD1BCD219CD32CD4-1CD52CD61" and x[value] <> 0 then 1 else
            if x[MasterCD] = "CD1BCD219CD32CD4-1CD51CD61" and Table.SelectRows(_, (y)=> y[MasterCD] = "CD1BCD219CD32CD4-1CD52CD61")[value]{0}? = 0 then 1
            else 0, Int64.Type), type table}}),
    CombinedAll = Table.Combine(GroupedRows[All])
in
    CombinedAll

[v-jingzhan-msft · Accepted solution]

Hi @kai1109 

Here is my solution based on your sample data. Hope it would be helpful!

To see how it works, you can create a blank query, open its Advanced Editor, copy and paste below code to replace any code there. 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WetwwwcjAyNjQyNAiPiIi4nHDxOLE3IKcVEclHSVDIyDh7GLo5OxiZGjp7GJs5Oxiomvo7GIKxGaGSrE6+PXj1G6Eot3Y0NDADEWzEwmWY9dtQMDqWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [name = _t, value = _t, MasterCD = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"name", type text}, {"value", Int64.Type}, {"MasterCD", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"name"}, {{"All", each _, type table [name=nullable text, value=nullable number, MasterCD=nullable text]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Condition3", each Table.SelectRows([All], each [MasterCD]="CD1BCD219CD32CD4-1CD52CD61" and [value]=0)),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom", each let vCondition3=Table.IsEmpty([Condition3]) in Table.AddColumn([All], "Flag", each if [MasterCD]="CD1BCD219CD32CD4-1CD52CD61" then (if [value]=0 then null else if [value] <> null then 1 else null) else if [MasterCD] = "CD1BCD219CD32CD4-1CD51CD61" then (if vCondition3=true then null else 1) else null)),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom1",{"Custom"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom", {"name", "value", "MasterCD", "Flag"}, {"name", "value", "MasterCD", "Flag"})
in
    #"Expanded Custom"

Result 

Best Regards,
Jing
If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!