Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
bigrods
Helper II
Helper II

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

IDRule 1Rule 5Rule 6
1234561 1
987654 11
24681  

 

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:

IDRule Violation
1234561
1234566
9876545
9876546
24681

 

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!

1 ACCEPTED SOLUTION
HotChilli
Super User
Super User

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.

 

View solution in original post

4 REPLIES 4
aj1973
Community Champion
Community Champion

@bigrods 

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

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

parry2k
Super User
Super User

@bigrods you should unpivot your table if you are looking something like this

 

parry2k_0-1593562096247.png

 

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.

HotChilli
Super User
Super User

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!

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.