Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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]

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.
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
Employee
Employee

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]

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.