Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello everyone,
I have the column "Words" that is the input and I´d like to get the strings in this column, sorted alphabetically and adding a sequence number in such way that the resulting Column "Sorted Words" be like below:
WORDS | SORTED WORDS |
BBBB | BBBB_1 |
GGGG | BBBB_2 |
DDDD | BBBB_3 |
BBBB | CCCC_1 |
GGGG | DDDD_1 |
CCCC | DDDD_2 |
KKKK | DDDD_3 |
CCCC | DDDD_4 |
GGGG | GGGG_1 |
CCCC | GGGG_2 |
KKKK | GGGG_3 |
CCCC | GGGG_4 |
KKKK | GGGG_5 |
DDDD | KKKK_1 |
BBBB | KKKK_2 |
KKKK | KKKK_3 |
DDDD | KKKK_4 |
My current codes does the easiest part, sort the column, but I don´t know how to add the sequence number to each group of words.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcgICpVidaCV3IAAzXIAAzMCUcgYCMMMbCFBFiFEDF8G0Ak0qFgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [WORDS = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"WORDS", type text}}),
#"Sorted Rows" = Table.Sort(#"Changed Type",{{"WORDS", Order.Ascending}})
in
#"Sorted Rows"
Solved! Go to Solution.
This article show you how to create row numbers for groups.
https://radacad.com/create-row-number-for-each-group-in-power-bi-using-power-query
After doing this you can create a custom column [SORTED WORDS]&"-"&[RowNumberColumn]
Hi,
This M code works
let
Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
Partition = Table.Group(Source, {"WORDS"}, {{"Partition", each Table.AddIndexColumn(_, "Index",1,1), type table}}),
#"Expanded Partition" = Table.ExpandTableColumn(Partition, "Partition", {"Index"}, {"Index"}),
#"Added Custom" = Table.AddColumn(#"Expanded Partition", "Custom", each [WORDS]&"_"&Number.ToText([Index])),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Index"}),
#"Sorted Rows" = Table.Sort(#"Removed Columns",{{"WORDS", Order.Ascending}, {"Custom", Order.Ascending}})
in
#"Sorted Rows"
Hope this helps.
Hi,
This M code works
let
Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
Partition = Table.Group(Source, {"WORDS"}, {{"Partition", each Table.AddIndexColumn(_, "Index",1,1), type table}}),
#"Expanded Partition" = Table.ExpandTableColumn(Partition, "Partition", {"Index"}, {"Index"}),
#"Added Custom" = Table.AddColumn(#"Expanded Partition", "Custom", each [WORDS]&"_"&Number.ToText([Index])),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Index"}),
#"Sorted Rows" = Table.Sort(#"Removed Columns",{{"WORDS", Order.Ascending}, {"Custom", Order.Ascending}})
in
#"Sorted Rows"
Hope this helps.
You are welcome.
This article show you how to create row numbers for groups.
https://radacad.com/create-row-number-for-each-group-in-power-bi-using-power-query
After doing this you can create a custom column [SORTED WORDS]&"-"&[RowNumberColumn]
Thanks so much for the link shared. It help me to get the desired output.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |