Forum Discussion
How to create a column with a specific row value.
Hi, I'm in a bind with power bi desktop. Currently the data is like this:
I'm trying to create a visual that shows the score of each area. Like this:
My issue is that the area is also listed in the rows, i'm trying to extract it to create a new column not sure how to proceed.
Jag_G12345 Attached pbix file FYR.
Few Power query transformation required to prepare the data in the tabular model.let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyNlHSUXIsSk0EUUqxOtFKQEZxcn5RqiGQYWiJImQEEjICCxmbmBqZIbQ6YWo1wdRqDhYyNzM3NUVodcbUaoyp1VApNhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Audit ID" = _t, Category = _t, Response = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Audit ID", Int64.Type}, {"Category", type text}, {"Response", type text}}), #"Filled Down" = Table.FillDown(#"Changed Type",{"Audit ID"}), #"Added Conditional Column" = Table.AddColumn(#"Filled Down", "Custom", each if Text.StartsWith([Category], "Area") then "Category" else "Response"), #"Pivoted Column" = Table.Pivot(#"Added Conditional Column", List.Distinct(#"Added Conditional Column"[Category]), "Category", "Response"), #"Removed Columns" = Table.RemoveColumns(#"Pivoted Column",{"Custom"}), #"Filled Down1" = Table.FillDown(#"Removed Columns",{"Area"}), #"Filtered Rows" = Table.SelectRows(#"Filled Down1", each ([score1] <> null)), #"Changed Type1" = Table.TransformColumnTypes(#"Filtered Rows",{{"score1", Int64.Type}, {"score2", Int64.Type}}) in #"Changed Type1"
If this post helps, then please consider Accept it as the solution
Thanks,
Kumar
2 Replies
- amitchandak
Super User
Jag_G12345 , In power query in Transform data -> split column by digit and non digit and create two columns
Split Column By Digit to Non Digit & Non Digit to Digit: https://youtu.be/tY4Yk1crS9s
Then use fill down -Fill Up Fill Down: https://youtu.be/mC2ps0pFqBI
then filter the rows with null response (number column)
and then use it
Jag_G12345 Attached pbix file FYR.
Few Power query transformation required to prepare the data in the tabular model.let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyNlHSUXIsSk0EUUqxOtFKQEZxcn5RqiGQYWiJImQEEjICCxmbmBqZIbQ6YWo1wdRqDhYyNzM3NUVodcbUaoyp1VApNhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Audit ID" = _t, Category = _t, Response = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Audit ID", Int64.Type}, {"Category", type text}, {"Response", type text}}), #"Filled Down" = Table.FillDown(#"Changed Type",{"Audit ID"}), #"Added Conditional Column" = Table.AddColumn(#"Filled Down", "Custom", each if Text.StartsWith([Category], "Area") then "Category" else "Response"), #"Pivoted Column" = Table.Pivot(#"Added Conditional Column", List.Distinct(#"Added Conditional Column"[Category]), "Category", "Response"), #"Removed Columns" = Table.RemoveColumns(#"Pivoted Column",{"Custom"}), #"Filled Down1" = Table.FillDown(#"Removed Columns",{"Area"}), #"Filtered Rows" = Table.SelectRows(#"Filled Down1", each ([score1] <> null)), #"Changed Type1" = Table.TransformColumnTypes(#"Filtered Rows",{{"score1", Int64.Type}, {"score2", Int64.Type}}) in #"Changed Type1"
If this post helps, then please consider Accept it as the solution
Thanks,
Kumar
