CODEWITHSUNDEEP
powerquerym
Henry
Henry

Reputation: 23

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:

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

Upvotes: 1

Views: 408

Answers (1)

horseyride
horseyride

Reputation: 21318

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

Upvotes: 0

Related Questions