separating values to multiple coulmns

[Anonymous]

Hi,

I have data in this format.

ID	Service
1	ABC
1	DEF
2	ABC
3	ABC
3	DEF
3	XYZ
4	DEF
5	DEF
5	ABC

I need output as 

ID	Service 1	Service 2	Service 3
1	ABC	DEF
2	ABC
3	ABC	DEF	XYZ
4	DEF
5	DEF	ABC

How can I possibly achieve this?

[amitchandak · Accepted solution]

@Anonymous , Create a Rank and then append it with service

Rank =
RANKX(FILTER('Table','Table'[ID]=EARLIER('Table'[ID])),'Table'[Service],,asc,dense)

But in 5th you have DEF before ABC that will not be taken care , for that have and index column and do rank on that
 https://stackoverflow.com/questions/45715963/creating-an-index-column-for-power-bi

Rank =RANKX(FILTER('Table','Table'[ID]=EARLIER('Table'[ID])),'Table'[index],,asc,dense)

Service like

service = "Service " & [Rank]

[v-jayw-msft · Accepted solution]

Hi @Anonymous ,

1# Add index column to the table in Query Editor.

2# Create calculated column like below.

level = 
var ranking = RANKX(
    FILTER (
        'Table',
        'Table'[ID] = EARLIER ( 'Table'[ID] )
    ),
    'Table'[Index],
    ,
    ASC,
    DENSE
)
return
SWITCH(ranking,1,"service1",2,"service2",3,"service3")

3# Create Matrix visual.

Best Regards,

Jay

Community Support Team _ Jay Wang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

I am not able to create this calculated column. Can you please help to elaborate more on this step. Thanks

This is the error I am getting.

[Ashish_Mathur]

Hi,

This M code works

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    Partition = Table.Group(Source, {"ID"}, {{"Partition", each Table.AddIndexColumn(_, "Index",1,1), type table}}),
    #"Expanded Partition" = Table.ExpandTableColumn(Partition, "Partition", {"Service", "Index"}, {"Service", "Index"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Partition", "Custom", each "Service "&Number.ToText([Index])),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Index"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Custom]), "Custom", "Service")
in
    #"Pivoted Column"

Hope this helps.

[mahoneypat]

Here is how you can do it in the query editor.  To see how it works, just create a blank query, go to Advanced Editor, and replace the text there with the M code below.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXJ0claK1YGwXVzdwGwjJHFjNDZMDYgdERkFZpsgiZuiscF6YwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Service = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", type text}, {"Service", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"ID"}, {{"AllRows", each _, type table [ID=text, Service=text]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Indexed", each Table.AddIndexColumn([AllRows], "ServiceNumber",1,1)),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"AllRows"}),
    #"Expanded Indexed" = Table.ExpandTableColumn(#"Removed Columns", "Indexed", {"Service", "ServiceNumber"}, {"Service", "ServiceNumber"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Indexed",{{"ServiceNumber", type text}}),
    #"Added Prefix" = Table.TransformColumns(#"Changed Type1", {{"ServiceNumber", each "Service " & _, type text}}),
    #"Pivoted Column" = Table.Pivot(#"Added Prefix", List.Distinct(#"Added Prefix"[ServiceNumber]), "ServiceNumber", "Service"),
    #"Changed Type2" = Table.TransformColumnTypes(#"Pivoted Column",{{"Service 1", type text}, {"Service 2", type text}, {"Service 3", type text}})
in
    #"Changed Type2"

. 

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[amitchandak · Accepted solution]

@Anonymous , Create a Rank and then append it with service

Rank =
RANKX(FILTER('Table','Table'[ID]=EARLIER('Table'[ID])),'Table'[Service],,asc,dense)

But in 5th you have DEF before ABC that will not be taken care , for that have and index column and do rank on that
 https://stackoverflow.com/questions/45715963/creating-an-index-column-for-power-bi

Rank =RANKX(FILTER('Table','Table'[ID]=EARLIER('Table'[ID])),'Table'[index],,asc,dense)

Service like

service = "Service " & [Rank]