Merging multiple conditions

[chinny]

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

[camargos88]

Hi @chinny ,

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.

[chinny]

Understood @camargos88 ,

I guess the idea would be to turn this into a template.

I'm unable to download the link?

Best.

[camargos88]

Hi @chinny ,

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"

[chinny · Accepted solution]

Many thanks @camargos88, that's pretty much what i'm looking for.