Forum Discussion
Adding new column with data from null row
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 ...
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