Adding new column with data from null row

Hi 0011AP ,

Please try this way.
Put all of this M function into the Advanced Editor:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclRw9vNW0lFCQbE60Uqm+ob6RgZGxkC+gZ4BhDI3BVKGhiDCVM8IxDEyAit2wmWKGYopJmDKAqTfCESYgdlA2dhYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, #"NS Hrs" = _t, #"NS Hrs target" = _t, #"Open Hrs" = _t, #"Selling Hrs" = _t, #"Selling Hrs target" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type text}, {"NS Hrs", type number}, {"NS Hrs target", type number}, {"Open Hrs", Int64.Type}, {"Selling Hrs", type number}, {"Selling Hrs target", Int64.Type}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each let 
    currentIndex = [Index],
    prevIndex = currentIndex - 1,
    prevDate = Table.SelectRows(#"Added Index", each [Index] = prevIndex){0}[Date],
    output = if Text.EndsWith([Date], "CNK") then "delete" else prevDate
    in 
    output),
        #"Filtered Rows" = Table.SelectRows(#"Added Custom", each [Custom] <> "delete"),
        #"Reordered Columns" = Table.ReorderColumns(#"Filtered Rows",{"Date", "Custom", "NS Hrs", "NS Hrs target", "Open Hrs", "Selling Hrs", "Selling Hrs target", "Index"}),
        #"Removed Columns" = Table.RemoveColumns(#"Reordered Columns",{"Index"})
in
    #"Removed Columns"

And the final output is as below:

I'm assuming that all of your department names end in CNK, but if they don't, please change the format that identifies all of them.

output = if Text.EndsWith([Date], "CNK") then "delete" else prevDate

Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.