Forum Discussion
Hadill
Helper I
Helper IData 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 g...
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"
MFelix
Super User
Super UserHi 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"