Swapping the values

[Anonymous]

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

[slorin]

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 

[slorin]

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
Sort

 Stéphane