Conditions in Power query

[Kamo]

Hi,

How to get the result in the "New Col"
The explanations are in the file,

Thanking you in advance

 

[GraH - Guido]

one way...

let
    Source = Excel.CurrentWorkbook(){[Name="Tableau3_2"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Id", Int64.Type}, {"Color", type text}, {"Kind", type text}, {"Resul", type logical}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
    #"Grouped Rows" = Table.Group(#"Added Index", {"Id", "Color"}, {{"All", each _, type table [Id=nullable number, Color=nullable text, Kind=nullable text, Resul=nullable logical, Index=number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each let
    List = [All][Resul]
  , First = List.First(List)
  , Others = List.Skip(List, 1) 
  , New = if First = false then List.Transform(Others, each  0) else List.Transform(Others, each 1)
  , Out = List.Combine({{1},New})
  , Zip = List.Zip({[All][Kind],[All][Resul],Out})
  in
  Zip),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
    #"Extracted Values" = Table.TransformColumns(#"Expanded Custom", {"Custom", each Text.Combine(List.Transform(_, Text.From), ";"), type text}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Extracted Values", "Custom", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"Kind", "Result", "Outcome"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Kind", type text}, {"Result", type logical}, {"Outcome", Int64.Type}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"All"})
in
    #"Removed Columns"

 

[Excel Wizard]

Please try

let
    Source = Excel.CurrentWorkbook(){[Name="Tableau3_2"]}[Content],
    Grouped = Table.Group(Source, "Resul", {{"N", each Table.AddColumn(Table.AddIndexColumn(_,"in"),"New Col", (n)=> if n[Resul] then 1 else if n[in] = 0 then 1 else 0) }},0),
    RemovedColumns = Table.RemoveColumns(Grouped,{"Resul"}),
    Expanded = Table.ExpandTableColumn(RemovedColumns, "N", {"Id", "Color", "Kind", "Resul", "New Col"})
in
    Expanded

 

[GraH - Guido]

You really love that function argument, right. I just don't get the hang of it.

 

[Kamo]

Hi Grah-Guido, Excel Wizard ,

Thank you very much for your help,

I have just tested the 2 solutions,
There is a small difference in the code in Grah-Guido (see the results of the 2 solutions in the attached file)

The Excel Wizard solution is perfect and works very well

This site is wonderful, I learn a lot from it

Thanks to you

Sincerly

 

[GraH - Guido]

Question of interpretation of your requirement

if Result=FALSE then New Col=1 for only the first row and 0 for the other

So what's the logic for these rows? Same pattern, other expected result?

 

[Kamo]

Hi Grah - Guido,

I'm sorry, you're right, my request is badly put,

I should have written :

if Result=FALSE then New Col=1 for only the first row
when there is more 1 row (id & Color) and 0 for the Other else 0 if there is only 1 row

if Result=TRUE then New Col=1 for all rows

Sincerly

 

[GraH - Guido]

Then 3 pink false/false should be 1/0 no?

 

[Kamo]

It's 0/0 because we only have "In" for pink in the Kind column,
If we had at least 1 "Out", the value would change to "true" and we would only have the value 1 for all rows for pink