Forum Discussion
Swapping the values
Hi,
I have two columns, Id and name with multiple values with delimitor and jumbled.I need to arrange it in order in power query editor or using DAX. Need your help to sort out the issue.
Input:
| ID | Names |
| 123 | AB(123) |
| 125 | CA(125) |
| 156|256 | GH(256)|HR(156) |
| 158|258|658 | OP(658)|AGM(258)|VP(158) |
| 489|894 | CO(489)|CDF(894) |
| 256 | JA(256) |
| 785|987|258 | PAT(987)|PA(258)|SA(785) |
Expected output
| ID | Names |
| 123 | AB(123) |
| 125 | CA(125) |
| 156|256 | HR(156)|GH(256) |
| 158|258|658 | VP(158)|AGM(258)|OP(658) |
| 489|894 | CO(489)|CDF(894) |
| 256 | JA(256) |
| 785|987|258 | SA(785)|PAT(987)|PA(258) |
Regards,
Harsha
2 Replies
Hi,
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("NY1BDoMwDAT/kpMtcYHi4BxdKkCVEFFb9YL4iR/fDaG39exove+h7W6hCXYnBA5HU4iAjAYiF5HonUTQeSEE9uVFgP9W0apHURhbJgR2m1eoCN8MVavaa3JNfZnfCAf7+JgIpNb1x9POHycZVDzpUPbRZPsQLvZsdfttBAPq8QM=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Names = _t]),
Fn_Sort = (Text1 as text, Text2 as text) as text=>
Text.Combine(
List.Sort(
Text.Split(Text2,"|"),
(x,y) =>
Value.Compare(
Text.PositionOf(Text1,Text.BetweenDelimiters(x, "(", ")")),
Text.PositionOf(Text1,Text.BetweenDelimiters(y, "(", ")"))
)
),
"|"
),
Sort = Table.AddColumn(Source, "Names_Sort", each Fn_Sort([ID], [Names]))
in
SortStéphane
Another solution with Table.ReplaceValue
= Table.ReplaceValue(PrevStep,each [ID],each [Names],(a, b, c)=>
Text.Combine(
List.Sort(
Text.Split(c,"|"),
(x,y) =>
Value.Compare(
List.PositionOf(Text.Split(b, "|"), Text.BetweenDelimiters(x, "(", ")")),
List.PositionOf(Text.Split(b, "|"), Text.BetweenDelimiters(y, "(", ")"))
)
),
"|"
),
{"Names"})Stéphane
