Forum Discussion
new column depending on value from another row
You can use Group By in Power Query (M).
I added a temporary Index to have the results sorted back to the original sort order, which may not be relevant for you.
You can generate initial code by grouping on "Code implantation" and "Fin réelle", with operation minimum for the service and operation "all rows", and then adjust the generated code (see below).
let Source = Table1, #"Added Index" = Table.AddIndexColumn(Source, "Index", 1, 1), #"Grouped Rows" = Table.Group(#"Added Index", {"Code implantation", "Fin réelle"}, {{"New Column", each List.Min({1,List.Count(List.Select([Service], each Text.StartsWith(_,"V")))}), Int64.Type}, {"AllData", each _, Value.Type(#"Added Index")}}), #"Expanded AllData" = Table.ExpandTableColumn(#"Grouped Rows", "AllData", {"Service", "Index"}), #"Reordered Columns" = Table.ReorderColumns(#"Expanded AllData",{"Code implantation", "Service", "Fin réelle", "New Column"}), #"Sorted Rows" = Table.Sort(#"Reordered Columns",{{"Index", Order.Ascending}}), #"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",{"Index"}) in #"Removed Columns"
You can use Group By in Power Query (M).
I added a temporary Index to have the results sorted back to the original sort order, which may not be relevant for you.
You can generate initial code by grouping on "Code implantation" and "Fin réelle", with operation minimum for the service and operation "all rows", and then adjust the generated code (see below).
let
Source = Table1,
#"Added Index" = Table.AddIndexColumn(Source, "Index", 1, 1),
#"Grouped Rows" = Table.Group(#"Added Index", {"Code implantation", "Fin réelle"},
{{"New Column", each List.Min({1,List.Count(List.Select([Service], each Text.StartsWith(_,"V")))}), Int64.Type},
{"AllData", each _, Value.Type(#"Added Index")}}),
#"Expanded AllData" = Table.ExpandTableColumn(#"Grouped Rows", "AllData", {"Service", "Index"}),
#"Reordered Columns" = Table.ReorderColumns(#"Expanded AllData",{"Code implantation", "Service", "Fin réelle", "New Column"}),
#"Sorted Rows" = Table.Sort(#"Reordered Columns",{{"Index", Order.Ascending}}),
#"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",{"Index"})
in
#"Removed Columns"
Thanks MarcelBeug
I struggled a bit to understand what was going on here and adapt the code.
It actually doesn't do exactly what I was after. It actually gives a value 0 where I was after a value 1 and vice versa for any Service that doesn't have a V at the beginning.
So I created a new calculated column based on the "New column" and it works well.
I tried to play a bit with the MAX/MIN to avoid this additional step but couldn't figure it out.
Anyway, that was an awesome tip and got the job done (and my brain suffer for a moment).
Thanks again!