Forum Discussion
Merging multiple conditions
Hi there,
Just discovered PowerBi so quite new to all this.
I'm trying to combine multiple conditions groups by names, and then split the columns to associate by the groups types if that makes sense.
With a bit of searching on here, I tested using the concatenate and then merging. This allowed be combine and then split the data, however all I have done is remove the relationships.
Failed output.
Name | Licence Groups | Plan | Status |
UserA | 1,1,1 | A,B,C | Active,Disabled,Active |
Orginal, from this
Name | Licence Groups | Plan | Status |
UserA | 1 | A | Active |
UserA | 1 | B | Disabled |
UserA | 1 | C | Active |
UserA | 2 | Pink | Disabled |
UserB | 2 | Pink | Pending |
UserB | 2 | Black | Pending |
UserB | 2 | Red | Pending |
To This if possible?
Name | Licence Groups | Plans | Status | Licence Groups1 | Plans1 | Status1 | Licence Groups2 | Plans2 | Status2 | Licence Groups3 | Plans3 | Status3 |
UserA | 1 | A | Active | 1 | B | Disabled | 1 | C | Active | 2 | Pink | Disabled |
UserB | 2 | Pink | Pending | 2 | Black | Pending | 2 | Red | Pending | Null | Null | Null |
Many thanks
Many thanks camargos88, that's pretty much what i'm looking for.
4 Replies
Hi Anonymous ,
Check this file: Download PBIX
You might think if this is the best way to model you data. With this model you are gonna have more work to handle calculations.
Understood camargos88 ,
I guess the idea would be to turn this into a template.
I'm unable to download the link?
Best.
Hi Anonymous ,
The link is active.
However, try this m code:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCi1OLXJU0lEyBGIQ7ZhcklmWqhSrgyrlBMQumcWJSTmpKRiSztj1GQFxQGZeNjatTqjyAal5KZl56RjSTjmJyfjkg4BGIsnGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Name = _t, #"Licence Groups" = _t, Plan = _t, Status = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Licence Groups", Int64.Type}, {"Plan", type text}, {"Status", type text}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Name"}, {{"Rows", each _, type table [Name=text, Licence Groups=number, Plan=text, Status=text]}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Rows], "Index", 1,1)),
#"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Custom"}),
#"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom", {"Name", "Licence Groups", "Plan", "Status", "Index"}, {"Name", "Licence Groups", "Plan", "Status", "Index"}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Expanded Custom", {"Index", "Name"}, "Attribute", "Value"),
#"Added Custom1" = Table.AddColumn(#"Unpivoted Other Columns", "Custom", each if [Index] > 1 then
[Attribute] & " " & Number.ToText([Index] - 1)
else [Attribute]),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Index", "Attribute"}),
#"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Custom]), "Custom", "Value")
in
#"Pivoted Column"
