Forum Discussion
Data Cleansing
Hi There,
Can I transfer the data in the below table to be undarstandable to the power bi?
I need to visualize the below KPI's and bring output just like:
*The count objectives under every goal
*The count of activites under every objective
*completeted Items (On the level of goals/objectives/activites)
I am not able to work with the data in the current structure. To visualize these KPI's, I changed the file manulay to the following structure:
But it will be extra work for me and will stuck with the manual update.
Do you have a specific/easer way to transform these Information to a way that can Power BI directly recognize/understand?
I hope it is clear for you.
Thanks a lot in advance.
Hi Hadill
Add 3 column to your data:
= Table.AddColumn(Source, "Goal", each if Text.Length([#"#"])= 1 then [Main Goals]& " " & [#"#"] else null) = Table.AddColumn(Source, "Objective", each if Text.Length([#"#"])= 1 then [Main Goals]& " " & [#"#"] else null) = Table.AddColumn(Source, "Activity", each if Text.Length([#"#"])= 1 then [Main Goals]& " " & [#"#"] else null)Do a fill down onm Goal and Objective
Filter the activyti Where is not null
Delete the Main goals and # from the dataset
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hY/BCoMwDIZfpfQswpInEA+7bYMdxUO39dDR0THCwLfXVlMUI72FfD/583WdPulKn4PxKg5t+Hy9Jav7aiJ1XF0fb/sk97cST4kmYkeDeKGGdQL2CVh3iIFiCRRKgBXjcDM/csb7QXHmNYe2tqmHwf6DTArNgh6DghZIWokg62DeCL9fAqk7TbKLHx5qoKSxkIP3UXo/E+FYPwI=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"#" = _t, #"Main Goals" = _t, Status = _t]), #"Added Custom" = Table.AddColumn(Source, "Goal", each if Text.Length([#"#"])= 1 then [Main Goals] & " " & [#"#"] else null), #"Added Custom1" = Table.AddColumn(#"Added Custom", "Objective", each if Text.Length([#"#"])= 3 then [Main Goals] & " "&[#"#"] else null), #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Activity", each if Text.Length( [#"#"] )= 5 then [Main Goals] &" " & [#"#"] else null), #"Filled Down" = Table.FillDown(#"Added Custom2",{"Goal", "Objective"}), #"Filtered Rows" = Table.SelectRows(#"Filled Down", each ([Activity] <> null)), #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"#", "Main Goals"}) in #"Removed Columns"
1 Reply
Hi Hadill
Add 3 column to your data:
= Table.AddColumn(Source, "Goal", each if Text.Length([#"#"])= 1 then [Main Goals]& " " & [#"#"] else null) = Table.AddColumn(Source, "Objective", each if Text.Length([#"#"])= 1 then [Main Goals]& " " & [#"#"] else null) = Table.AddColumn(Source, "Activity", each if Text.Length([#"#"])= 1 then [Main Goals]& " " & [#"#"] else null)Do a fill down onm Goal and Objective
Filter the activyti Where is not null
Delete the Main goals and # from the dataset
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hY/BCoMwDIZfpfQswpInEA+7bYMdxUO39dDR0THCwLfXVlMUI72FfD/583WdPulKn4PxKg5t+Hy9Jav7aiJ1XF0fb/sk97cST4kmYkeDeKGGdQL2CVh3iIFiCRRKgBXjcDM/csb7QXHmNYe2tqmHwf6DTArNgh6DghZIWokg62DeCL9fAqk7TbKLHx5qoKSxkIP3UXo/E+FYPwI=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"#" = _t, #"Main Goals" = _t, Status = _t]), #"Added Custom" = Table.AddColumn(Source, "Goal", each if Text.Length([#"#"])= 1 then [Main Goals] & " " & [#"#"] else null), #"Added Custom1" = Table.AddColumn(#"Added Custom", "Objective", each if Text.Length([#"#"])= 3 then [Main Goals] & " "&[#"#"] else null), #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Activity", each if Text.Length( [#"#"] )= 5 then [Main Goals] &" " & [#"#"] else null), #"Filled Down" = Table.FillDown(#"Added Custom2",{"Goal", "Objective"}), #"Filtered Rows" = Table.SelectRows(#"Filled Down", each ([Activity] <> null)), #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"#", "Main Goals"}) in #"Removed Columns"