Adding rows by date

Hi Jodallen123 I suppose you are asking how to create the end date column right 

Create a custom column for the "end date" of each status by shifting the "Created_date" column of the next row upwards:

Go to the "Add Column" tab -> "Custom Column" -> Formula

End_Date = Table.AddColumn(#"Previous Step", "End_Date", each Table.RowAfter([Created_dat

e]))

Because you mentioned you have multiple order numbers, I would start by 

• Group by Order Number
• Custom aggregation:
  • Add Index Column
  • Add column with a list of the requisite rows
• Then expand everything.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjAyNDEyV9JR8szLLMlMzFEILkksKS0GCgSXpBYoGAIZBkBsZGBkrGtgpGsEEYjVQdIJVwhmGKHrMAZxsOgwgjGMUXSANAE55lh0GMMYJug6QDKGWHSYwBimKDpMdA0tgBwTM6XYWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [orderNumber = _t, status_changed_from = _t, status_changed_to = _t, current_status = _t, Created_date = _t, days_with_status = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"orderNumber", Int64.Type}, {"status_changed_from", type text}, {"status_changed_to", type text}, {"current_status", Int64.Type}, {"Created_date", type date}, {"days_with_status", Int64.Type}}),
    
    #"Grouped Rows" = Table.Group(#"Changed Type", {"orderNumber"}, {
        {"New Table",(t)=>
            [a=Table.AddIndexColumn(t,"Index",0,1,Int64.Type),
             b=Table.AddColumn(a,"Created Dates", each 
                    try List.Dates([Created_date],a[days_with_status]{[Index]+1}, #duration(1,0,0,0)) 
                    otherwise {[Created_date]}),
             c=Table.RemoveColumns(b,{"Index"})]
                [c],
            type table[orderNumber=Int64.Type, status_changed_from=text, status_changed_to=text, current_status=Int64.Type,
                    Created_date=date, days_with_status=Int64.Type, Created Dates={date}]
        
        }}),

    #"Expanded New Table" = Table.ExpandTableColumn(#"Grouped Rows", "New Table", 
        {"status_changed_from", "status_changed_to", "current_status", "Created_date", "days_with_status", "Created Dates"}),
    
    #"Expanded Created Dates" = Table.ExpandListColumn(#"Expanded New Table", "Created Dates"),
    
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Created Dates",{"Created_date"}),
    
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",
        {"orderNumber", "status_changed_from", "status_changed_to", "current_status", "Created Dates", "days_with_status"})
in
    #"Reordered Columns"

Hi Jodallen123 

to solve this problem, you can use the following code in the advance editor

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjAyNDEyV9JR8szLLMlMzFEILkksKS0GCgSXpBYoGAIZBkBsZGBkrGtgpGsEEYjVQdIJVwhmGKHrMAZxsOgwgjGMUXSANAE55lh0GMMYJug6QDKGWHSYwBimKDpMdA0tgBwTM6XYWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [orderNumber = _t, status_changed_from = _t, status_changed_to = _t, current_status = _t, Created_date = _t, days_with_status = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"orderNumber", Int64.Type}, {"status_changed_from", type text}, {"status_changed_to", type text}, {"current_status", Int64.Type}, {"Created_date", type date}, {"days_with_status", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each [a=List.RemoveFirstN(#"Changed Type"[Created_date], (x)=>x<[Created_date]),b=try List.Dates(a{0},Number.From(a{1}-a{0}),Duration.From(1)) otherwise a][b]),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom")
in
    #"Expanded Custom"