Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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?
Solved! Go to 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]
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.
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.
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.
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.
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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
@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]
User | Count |
---|---|
140 | |
113 | |
104 | |
76 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |