Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Solved! Go to Solution.
@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
@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
@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
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |