Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
Hi there, I searched but couldn't find anything relating to this. Hope someone could please help:
I have a table with and ID field and numerous fields for various Rules with a 1 or null (1 if the rule is violated, null if not). I'd like to be able to analyse this data in a table or matrix to see the no's and types of violations etc but am having difficulty due to the fact there are multiple "Rule" fields - is there a way of taking all the fields and making 1 table to make this easier? (or any other way?)
e.g. Current data
ID | Rule 1 | Rule 5 | Rule 6 |
123456 | 1 | 1 | |
987654 | 1 | 1 | |
2468 | 1 |
I'd like to do some analysis on the no's of Rules violated, so was thinking I might need to have my data like this:
ID | Rule Violation |
123456 | 1 |
123456 | 6 |
987654 | 5 |
987654 | 6 |
2468 | 1 |
How do I get from the current data to the suggested data, or is there another way of doing it?
Thanks so much in advance!
Solved! Go to Solution.
Use Power Query. I suggest selecting all the Rule columns and replace null with 0.
Then , again with all rule columns selected, choose 'Unpivot' from the transform menu.
That will give 3 columns ID, Attribute, Value. You can rename if required.
Pull ID and Attribute on to a table visual (and Value if required). Use the filter pane to select Value = 1 for rule violations.
In Power query editor select the last 2 columns of your table and use Unpivot columns like in the img
Regards
Regards
Amine Jerbi
If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook
@bigrods you should unpivot your table if you are looking something like this
here is the power query, start blank query, click advanced editor and copy the following code
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8nRR0lEKKs1JVTCEMUxhDDOlWJ1oJUMjYxNTM6AYSIECmAYJW1qYm5mawIVgwkYmZhZQARCKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, #"(blank).1" = _t, #"(blank).2" = _t, #"(blank).3" = _t]),
#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"ID", Int64.Type}, {"Rule 1", Int64.Type}, {"Rule 5", Int64.Type}, {"Rule 6", Int64.Type}}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"ID"}, "Attribute", "Value")
in
#"Unpivoted Other Columns"
I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!
⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Use Power Query. I suggest selecting all the Rule columns and replace null with 0.
Then , again with all rule columns selected, choose 'Unpivot' from the transform menu.
That will give 3 columns ID, Attribute, Value. You can rename if required.
Pull ID and Attribute on to a table visual (and Value if required). Use the filter pane to select Value = 1 for rule violations.
Great, thanks so much this has worked!
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
112 | |
94 | |
92 | |
82 | |
69 |
User | Count |
---|---|
161 | |
129 | |
125 | |
108 | |
97 |