Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
95 | |
77 | |
65 | |
53 |
User | Count |
---|---|
144 | |
105 | |
102 | |
89 | |
63 |