Forum Discussion
Data Cleaning
Hi Community,
My source excel file has data merged into once column. I want to do the data cleaning within Power BI.
The data looks like this in one column after importing into Power BI:
Task - 000010 RCRA Report
4/19/2017
8/2/2018
9/5/2015
Task - 50002 Groundwater Report
12/6/2017
1/11/2018
Task - 50003 Ecological Report
8/8/2017
and so on...
Essentially, the task information should be in one column and the dates should be in another column. I thought about duplicating the column and using find/replace for both. Howerver, I have too many different tasks and dates to use find and replace easily. Is there a way to separate the two different types of data into two columns: one column with dates and the other with tasks? Thank you for the help!
1.add conditional column to extract "task" and "date" column
2.fill down in the task column
3.delete the origianal column, then select rows which is not null in the "date" column.
Code in Advanced editor
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCkkszlbQVTAAAkMDhSDnIEeFoNSC/KISpVidaCUTfUNLfSMDQ3Mwz0LfCMSxAHMs9U1BHFMwB2qKKdAUIwX3ovzSvJTyxJLUImSzDI30zRBmGeobGiIMA+uHGmCs4Jqcn5OfnpmcmIOs30LfAqo9FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}), #"Added Conditional Column" = Table.AddColumn(#"Changed Type", "task", each if Text.Contains([Column1], "Task") then [Column1] else null), #"Added Conditional Column1" = Table.AddColumn(#"Added Conditional Column", "date", each if not Text.Contains([Column1], "Task") then [Column1] else null), #"Filled Down" = Table.FillDown(#"Added Conditional Column1",{"task"}), #"Removed Columns" = Table.RemoveColumns(#"Filled Down",{"Column1"}), final=Table.SelectRows(#"Removed Columns",each[date]<>null) in finalBest Regards
Maggie
2 Replies
Hello,
Do each of the task rows actually begin with the word task? If that is the case, when you bring in your data you could write an IF statement in a new column to only include rows where the cell contains "task", and leave blank otherwise. You could then write another calculated column to extract the date from your original column, only if the task column you just created is blank.
1.add conditional column to extract "task" and "date" column
2.fill down in the task column
3.delete the origianal column, then select rows which is not null in the "date" column.
Code in Advanced editor
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCkkszlbQVTAAAkMDhSDnIEeFoNSC/KISpVidaCUTfUNLfSMDQ3Mwz0LfCMSxAHMs9U1BHFMwB2qKKdAUIwX3ovzSvJTyxJLUImSzDI30zRBmGeobGiIMA+uHGmCs4Jqcn5OfnpmcmIOs30LfAqo9FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}), #"Added Conditional Column" = Table.AddColumn(#"Changed Type", "task", each if Text.Contains([Column1], "Task") then [Column1] else null), #"Added Conditional Column1" = Table.AddColumn(#"Added Conditional Column", "date", each if not Text.Contains([Column1], "Task") then [Column1] else null), #"Filled Down" = Table.FillDown(#"Added Conditional Column1",{"task"}), #"Removed Columns" = Table.RemoveColumns(#"Filled Down",{"Column1"}), final=Table.SelectRows(#"Removed Columns",each[date]<>null) in finalBest Regards
Maggie
