Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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 ?
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
When I tried this is show as Table and not the department vaule, do you knwo I can convert this?
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"})
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.