Adding new column with data from null row

[0011AP]

I have data that list the name of the department in a row that is all null but for that one cell. I need to take the department name and make a new column with it list next to the dates. I have more then one row that has a different department name that needs to do the same. 

How do I take the department name CNK and add it a new column next to the date. and repeat that when an different department data shows  lower on the list ?

[dufoq3]

Hi @0011AP, another solution:

Result

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lY5JCoAwDEWvIlmXDrHjUrsUvEDp/a9hE1cFlQghP8PL0BpsSz0PUDBZVw2CcQYtriO32t6SwhDnyAWNlCAyHP/A+9vJeYtnyTSP5CLHo0tokqNZjlbZZ4Gl0DhVXOIY/cNnn2iWo0WI9gs=", 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]),
    ChangedType = 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}}, "en-US"),
    Ad_DepartmentName = Table.AddColumn(ChangedType, "Department Name", each 
        [ a = Record.ToList(Record.RemoveFields(_, "Date")),
          b = if List.NonNullCount(a) = 0 then [Date] else null
        ][b], type text),
    FilledDown = Table.FillDown(Ad_DepartmentName,{"Department Name"})
in
    FilledDown

[0011AP]

When I tried this is show as Table and not the department vaule, do you knwo I can convert this?

[dufoq3]

I do not see your data. Could you paste whole query here?

[wdx223_Daniel]

NewStep=Table.SelectColumns(Table.FromPartitions("Department",Table.ToRows(Table.Group(YourData,"Date",{"n",each Table.Skip(_)},0,(x,y)=>Byte.From(y is text)))),List.InsertRange(Table.ColumnNames(YourData),1,{"Department"})

[Anonymous]

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.