Skip to main content
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Advocate I
Advocate I

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?




Community Support
Community Support

Hi @imaddrell

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

    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]



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



Best Regards



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

Helpful resources

Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.