Forum Discussion
separating values to multiple coulmns
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?
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-biRank =RANKX(FILTER('Table','Table'[ID]=EARLIER('Table'[ID])),'Table'[index],,asc,dense)
Service likeservice = "Service " & [Rank]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.
5 Replies
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-biRank =RANKX(FILTER('Table','Table'[ID]=EARLIER('Table'[ID])),'Table'[index],,asc,dense)
Service likeservice = "Service " & [Rank]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
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.
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.
