Forum Discussion
Combining and merging fields on same table?
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!
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.
4 Replies
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!
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.⚡
