Data Cleansing

[Hadill]

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.

[MFelix · Accepted solution]

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"