Forum Discussion
Populating a blank date
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
2 Replies
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 col5If you have any question in your sceniora, please ask me.
Best Regards
Maggie
