Combining and merging fields on same table?

[bigrods]

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!

[aj1973]

@bigrods 

In Power query editor select the last 2 columns of your table and use Unpivot columns like in the img

Regards

[parry2k]

@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.⚡

[HotChilli · Accepted 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.

[bigrods]

Great, thanks so much this has worked!