cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

separating values to multiple coulmns

Hi,

I have data in this format.

IDService
1ABC
1DEF
2ABC
3ABC
3DEF
3XYZ
4DEF
5DEF
5ABC

 

I need output as 

 

IDService 1Service 2Service 3
1ABCDEF 
2ABC  
3ABCDEFXYZ
4DEF  
5DEFABC 

 

How can I possibly achieve this?

2 ACCEPTED SOLUTIONS
amitchandak
Super User
Super User

@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]
Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin

Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!

View solution in original post

v-jayw-msft
Community Support
Community Support

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.

1.PNG

 

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.

 

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.

View solution in original post

5 REPLIES 5
v-jayw-msft
Community Support
Community Support

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.

1.PNG

 

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.

 

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.

View solution in original post

Anonymous
Not applicable

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.PowerBI_error.JPG

Ashish_Mathur
Super User
Super User

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.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
mahoneypat
Super User
Super User

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





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


amitchandak
Super User
Super User

@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]
Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin

Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!

View solution in original post

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.