Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

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: ...
Need Help
Tips and Tricks
slorin's avatar
slorin
Super User
3 years ago

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