Need Dates in Same Row for Each Project in Power Query

Question

Hi Guys,This is my data in Power Query.Project ID&nbsp; &nbsp; Status&nbsp; &nbsp; Status1Date&nbsp; &nbsp; Status2Date&nbsp; &nbsp; Status3Date&nbsp; &nbsp; Status4Date1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Status1&nbsp; &nbsp; 1/1/2022&nbsp; &nbsp; &nbsp;&nbsp;2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Status1&nbsp; &nbsp; 2/5/20223&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Status2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 5/5/20224&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Status3&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;3/23/20225&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Status4&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;7/11/20221&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Status2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 3/3/20221&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Status3&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;5/6/20223&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Status4&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 8/3/2022&nbsp;&nbsp;I need output like the below...Project ID&nbsp; &nbsp; Status&nbsp; &nbsp; Status1Date&nbsp; &nbsp; Status2Date&nbsp; &nbsp; Status3Date&nbsp; &nbsp; Status4Date1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Status1&nbsp; &nbsp; 1/1/2022&nbsp; &nbsp; &nbsp;&nbsp;3/3/2022&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;5/6/20222&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Status1&nbsp; &nbsp; 2/5/20223&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Status2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 5/5/2022&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;8/3/20224&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Status3&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;3/23/20225&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Status4&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;7/11/2022&nbsp;&nbsp;Here I have used Group By of "Project ID" and have taken min of all statuses dates. I am getting correct result.But Sometimes I may be back to the previous status like&nbsp;&nbsp;Project ID&nbsp; &nbsp; Status&nbsp; &nbsp; Status1Date&nbsp; &nbsp; Status2Date&nbsp; &nbsp; Status3Date&nbsp; &nbsp; Status4Date1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Status1&nbsp; &nbsp; 1/1/2022&nbsp; &nbsp; &nbsp;&nbsp;2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Status1&nbsp; &nbsp; 2/5/20223&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Status2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 5/5/20224&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Status3&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;3/23/20225&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Status4&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;7/11/20221&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Status2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 3/3/20221&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Status3&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;5/6/20223&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Status4&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 8/3/20221&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Status2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;8/8/2022&nbsp;In this case I need this column also need to show in the final table likeProject ID&nbsp; &nbsp; Status&nbsp; &nbsp; Status1Date&nbsp; &nbsp; Status2Date&nbsp; &nbsp; Status3Date&nbsp; &nbsp; Status4Date1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Status1&nbsp; &nbsp; 1/1/2022&nbsp; &nbsp; &nbsp;&nbsp;3/3/2022&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;5/6/20221&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Status1&nbsp; &nbsp; &nbsp;1/1/2022&nbsp; &nbsp; &nbsp;8/8/2022&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;5/6/20222&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Status1&nbsp; &nbsp; 2/5/20223&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Status2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 5/5/2022&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;8/3/20224&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Status3&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;3/23/20225&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Status4&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;7/11/2022&nbsp;&nbsp;&nbsp;How can I get it in Power Query?Could you please help me?&nbsp;&nbsp;Thanks,Suneel K.&nbsp;

ba_pete · Answer

Hi&nbsp;suneelkethagani&nbsp;,
&nbsp;
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
&nbsp;
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
&nbsp;
I get the following output:

&nbsp;
Pete

Forum Discussion

Need Dates in Same Row for Each Project in Power Query

1 Reply

Related content

Renaming a Power BI project file (.pbip)

Power BI Project file (PBIP)

Order Projects by date

Power Query dates

Find Most Recent Date Relative to another Date Column in Power Query