Adding rows by date

[Jodallen123]

Hi everyone,

I have data that looks like this (this is of course just one ordernumber, in reality there are thousands):

orderNumber	status_changed_from	status_changed_to	current_status	Created_date	days_with_status
021427	Initial Status	Step 1	0	2023-02-21	0
021427	Step 1	Step 2	0	2023-02-23	2
021427	Step 2	Step 3	0	2023-03-02	7
021427	Step 3	Step 4	0	2023-03-03	1
021427	Step 4	Step 5	0	2023-04-18	46

For example the top row was created 2023-02-21 and didn't change status until 2023-02-23, this means that I want one more row for that step, one with 2023-02-22 and then the next step "takes over" on 2023-02-23 and so on. Something like this:  

Does anybody have any idea how this transformation can be done in power query? 

Thanks in advance and have a nice day! 

[Akash_Varuna · Accepted solution]

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]))

[ronrsnfld · Accepted solution]

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"

[v-priyankata]

Hi @Jodallen123 
I hope this information is helpful. Please let me know if you have any further questions or if you'd like to discuss this further. If this answers your question, please Accept it as a solution and give it a 'Kudos' so others can find it easily.
Thank you.

[v-priyankata]

Hi @Jodallen123
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank you.

[v-priyankata]

Hi @Jodallen123 

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[Omid_Motamedise · Accepted solution]

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"

[ronrsnfld · Accepted solution]

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"

[Akash_Varuna]

Hi @Jodallen123 Could you try this please 

• Add End Date Column: Create a column with the next row's Created_date as End_Date. Fill missing values for the last row.

• Generate Date Range: Add a column to generate dates between Created_date  and  End_Date - 1 :

  List.Dates([Created_date], Duration.Days([End_Date] - [Created_date]), #duration(1, 0, 0, 0))

• Expand Dates: Expand the date list into new rows.

• Add Status Columns: Retain original columns for each expanded row.
  If this post helped please do give a kudos and accept this as a solution
  Thanks In Advance

[Jodallen123]

Hi,

Thanks alot! 

I thought about adding an end_date column as well, if I had that I think I can manage the rest, I am however unsure how that can be done. Have you or anyone else here done something similar and have any input? 

[Akash_Varuna · Accepted solution]

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]))