Power Query Formula

[millhouse123]

How would I create the following formula in power query? I understand how to do a basic conditional formula but I am stuck on the part of the formula that looks at all rows above the current row. So basically the sumproduct part of this formula is where I need help.

Thanks in advance.


=IF(F2="Last Requirements",(IF(SUMPRODUCT(($B$2:$B2=B2)*($F$2:$F2=F2))>1,0,1)),0)

 

[MarcelBeug]

So for each value in B, you want a 1 in each first row with "Last Requirements" in column F; otherwise 0.

A solution closest to the Excel solution is adding an Index column and use that to select the previous values:

let
    Source = Table1,
    Indexed = Table.AddIndexColumn(Source, "Index", 0, 1),
    #"Added Custom" = Table.AddColumn(Indexed, "PowerQuery", (x) => if x[F] <> "Last Requirements" then 0 else if Table.RowCount(Table.SelectRows(Indexed, each [B] = x[B] and [F] = x[F] and [Index] <= x[Index])) > 1 then 0 else 1),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Index"})
in
    #"Removed Columns"

However, this is not a very efficient solution in Power Query.
Much faster is to group the data as in the following solution:

let
    Source = Table1,
    #"Added Index" = Table.AddIndexColumn(Source, "Sort", 0, 1),
    #"Grouped Rows" = 
        Table.Group(#"Added Index", 
                    {"B", "F"}, 
                    {{"AllData", 
                      each 
                      let 
                        #"Added Index1" = Table.AddIndexColumn(_, "Index", 0, 1),
                        #"Added Custom" = Table.AddColumn(#"Added Index1", "PowerQuery", each if [Index] = 0 and [F] = "Last Requirements" then 1 else 0)
                      in
                        #"Added Custom", type table}}),
    #"Expanded AllData" = Table.ExpandTableColumn(#"Grouped Rows", "AllData", {"C", "D", "E", "Excel", "Sort", "PowerQuery"}, {"C", "D", "E", "Excel", "Sort", "PowerQuery"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Expanded AllData",{"B", "C", "D", "E", "F", "Excel", "Sort", "PowerQuery"}),
    #"Sorted Rows" = Table.Sort(#"Reordered Columns",{{"Sort", Order.Ascending}}),
    #"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",{"Sort"})
in
    #"Removed Columns"