Skip to main content
cancel
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

Reply
imaddrell
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:

Capture.PNG

 

 

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 2
v-juanli-msft
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

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

3.png

 

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

 

 

Best Regards

Maggie

 

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

Announcements
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.