Power Query - Data Transformation

Question

New to PowerBi and Power Query and having some trouble transforming the data.

The data contains processes for each sale category with status if the manufacturing process has been complete or not. Require a new aggregate table that has three calculated columns returning the following dates:

• Start date which is defined as the first date the process enters the table
• Predicted end date which is defined as the last date the process is shown in the table
• Actual end date which is defined as the last instance the process status is equal to "Done"

Have managed to return the three dates but each ends up on a separate line rather than one line with the data. Below is the original data and required output.

Output Table

Would appreciate any assistance in transforming this data.

Answer 1

Most of it you can do using the Power Query UI:

• Group by Month/Category/Process
• Aggregations:
  • Start date => Min of Date
  • Estimated (or Predicted) end date => Max of Date

But then you need a custom aggregation where you determine the max date after filtering the subtable for "Done" in the status column.

You can do that in the Advanced Editor editing the M Code directly.

M Code

let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Month", type date}, 
                            {"Category", type text}, {"Process", type text}, {"Status", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Month", "Category", "Process"}, {
        {"Start Date", each List.Min([Date]), type nullable date}, 
        {"Predicted End Date", each List.Max([Date]), type nullable date},

    //Custom aggregation to calculate Actual End Date
    //Note that we can Filter the table here, and then select the last date
        {"Actual End Date", each List.Max(Table.SelectRows(_, each [Status]= "Done")[Date]), type nullable date}        
        })
in
    #"Grouped Rows"

Original Data

Results