Need Dates in Same Row for Each Project in Power Query

Hi suneelkethagani ,

Paste the following code into a new blank query using Advanced Editor to see the steps I took:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bY5bDoAgDATvwjcJfYByF8L9r+HWVLRqUpKW6U46RuKUz0dcUEIiGLxmHkkuLoXaD1c0/gUeV4xXNLoiooV0rRhvaOqthGUv7IcY54c/hD3PwW8nbMGvH39flrcfpAf/PAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Project ID" = _t, Status = _t, Status1Date = _t, Status2Date = _t, Status3Date = _t, Status4Date = _t]),
    chgTypes = Table.TransformColumnTypes(Source,{{"Project ID", Int64.Type}, {"Status", Int64.Type}, {"Status1Date", type date}, {"Status2Date", type date}, {"Status3Date", type date}, {"Status4Date", type date}}),
    remStatusCol = Table.RemoveColumns(chgTypes,{"Status"}),
    groupProjectID = Table.Group(remStatusCol, {"Project ID"}, {{"data", each _, type table [Project ID=nullable number, Status=nullable number, Status1Date=nullable date, Status2Date=nullable date, Status3Date=nullable date, Status4Date=nullable date]}}),
    fillDownNested = Table.TransformColumns(groupProjectID, {"data", each Table.FillDown(_, {"Status1Date", "Status2Date", "Status3Date", "Status4Date"})}),
    fillUpNested = Table.TransformColumns(fillDownNested, {"data", each Table.FillUp(_, {"Status1Date", "Status2Date", "Status3Date", "Status4Date"})}),
    nestedTableDistinct = Table.TransformColumns(fillUpNested, {"data", each Table.Distinct(_)}),
    expandDataCol = Table.ExpandTableColumn(nestedTableDistinct, "data", {"Status1Date", "Status2Date", "Status3Date", "Status4Date"}, {"Status1Date", "Status2Date", "Status3Date", "Status4Date"})
in
    expandDataCol

STEP SUMMARY:

remStatusCol = Remove the status column as no longer required

groupProjectID = Group table on [Project ID] and use an 'All Rows' aggregation to create nested project tables

fillDownNested/fillUpNested = just fill all cells in nested tables with dates

nestedTableDistinct = Remove all duplicate rows from nested tables

expandDataCol = Reinstate nested table columns to the main table

I get the following output:

Pete