Populating a blank date

[Anonymous]

Hi All

Let me see if I can explain this problem. I have a table that contains one row per event, each row has columns of dates that the event changed a status. My data looks like this:

I want to populate each of the blank columns with the next date in the row. In the case of Event 1, I want each of Blank row / cells to be populated with the date from the far right in Column 6.

In the case of Event 2 I want Column 2 to be popluted with data from Column 3 and Column 5 to be populated with the date from Column 6.

Could anyone help me with this? I think I need something similar to lastnonblank combined with an IF statement e.g. if column 2 = Blank then Lastnonblank from column 3.

Any ideas?

Thanks

Ian

[v-juanli-msft]

Hi @Anonymous

You could transform the column from right to left one by one, below introduce populating null values of "col5" with values of "col6"

In query editor, write code in advanced editor

let
    Source = Excel.Workbook(File.Contents("C:\Users\maggiel\Desktop\case\7\7.19\7.19.xlsx"), null, true),
    Sheet3_Sheet = Source{[Item="Sheet3",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Sheet3_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"event", type text}, {"col1", type date}, {"col2", type any}, {"col3", type date}, {"col4", type date}, {"col5", type date}, {"col6", type date}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Changed Type",{"event", "col1", "col2", "col3", "col4", "col5", "col6"}),
    new1=Table.TransformColumns(#"Reordered Columns",{"col5",each if _ =null then Table.ReplaceValue(#"Reordered Columns", each[col5], each[col6], Replacer.ReplaceValue,{"col5"}) else _}),
    col5 = new1{0}[col5]
in
    col5

If you have any question in your sceniora, please ask me.

Best Regards

Maggie

[Anonymous]

That looks like a great solution, but can the solution be adapted to make calculated columns as opposed to using the query editor?