new column depending on value from another row

[SuperSayan]

Hi everyone,

My dashboard is almost completed but I'm hitting a last minute bump road.

My main data table is a record of intervention performed by technicians.

I have different type of interventions, some are the main ones and some are less important.

What I would like to do is to be able to create a new column with the following criteria (I'm struggling with the bold part):

- if the "service" is starting with "V" ---> value is 1(this is easy)

- if the service isn't starting with "V"

  ---> check if there is a "service" starting with "V" in the table for the same "Fin réelle" and "code implantation"

                 ---> if Yes, value is 0

                 ---> if No, value is 1

I'm open to any different way to address this.

Below a sample of the data:

[MarcelBeug · Accepted solution]

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"

[SuperSayan]

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!