Search code examples
powerquerym

How to perform a calculation in Power Query based on a group of values in different columns?

I have an example table as below:

enter image description here

I would like to add a calculated column in Power Query as follows:

  • for each pair of ID1 and ID2, the calculated column should insert into the respective row the Date of the next Stage Order.
  • if any certain pair of ID1 and ID2 has only 1 stage, then calculated Column is blank

I managed to do that in DAX but could not find any solution in M - Power Query. Any help is highly appreciated. Thank you!

Expected result as below: enter image description here

Solution

  • You can try this in powequery, pasted into home..advanced editor..

    let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Grouped Rows" = Table.Group(Source, {"ID1", "ID2"}, {{"data", each 
    let a= Table.AddIndexColumn(_, "Index", 0, 1, Int64.Type)
    in Table.AddColumn(a, "OffsetDate", each try a{[Index]+1}[Date] otherwise null)
, type table}}),
#"Expanded data" = Table.ExpandTableColumn(#"Grouped Rows", "data", {"Stage Order", "Date", "OffsetDate"}, {"Stage Order", "Date", "OffsetDate"}),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded data",{{"Date", type date}, {"OffsetDate", type date}})
in  #"Changed Type"

    enter image description here

    It groups, so ID1 and ID2 are together, adds an index, takes the Date from the index of the row below, then expands to get back all the columns. This assumes the data comes in sorted as you want, otherwise do that first