Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

M Help!

 

Hi all,

I am trying to acheive a logic in Power Query. 

 

Lets consider the below sample data. Series is the source data, and i want to convert the source data as mentioned in the second column, which is expected output.

Series column can have values , seperated by | and it can come in any order as shown here, but in the output it should be ordered as needed. 

Any help will be highly appriciated.

 

Series ExpectedOutput
A|D|B A|B|D
C|A|E|D A|C|D|E
D|B B|D
E|D|A A|D|E
D D
D|E|B|A|C A|B|C|D|E

 

Thanks

Raj

1 ACCEPTED SOLUTION
AlB
Super User
Super User

 

I thought this, a small variation of what we had before, would work but it doesn't. I haven't had the time to fix it:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("PczBDQAgCAPAVQxvl0BhCuImHd4q6A/K0QhRGEaTzmHAZPWQCYXDMpy8+43LPUUBTfPFWa2ws4/vr7p61gY=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Series " = _t, ExpectedOutput = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Series ", type text}, {"ExpectedOutput", type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"ExpectedOutput"}),
    #"Trimmed Text" = Table.TransformColumns(#"Removed Columns",{{"Series ", Text.Trim, type text}}),
    #"Added Custom" = Table.AddColumn(#"Trimmed Text", "Custom", each Text.Combine(List.Sort(Text.Split([#"Series "],"|"),  List.PositionOf({"C","E","D","B","A","K","F"}, [#"Series "])),"|")),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Custom",{"Series "})
in
    #"Removed Columns1"

So try this other approach instead, less elegant but effective: 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("PczBDQAgCAPAVQxvl0BhCuImHd4q6A/K0QhRGEaTzmHAZPWQCYXDMpy8+43LPUUBTfPFWa2ws4/vr7p61gY=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Series " = _t, ExpectedOutput = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Series ", type text}, {"ExpectedOutput", type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"ExpectedOutput"}),
    #"Trimmed Text" = Table.TransformColumns(#"Removed Columns",{{"Series ", Text.Trim, type text}}),
    #"Added Custom" = Table.AddColumn(#"Trimmed Text", "Custom", each Table.FromList(Text.Split([#"Series "],"|"))),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Custom",{"Series "}),
    #"Added Custom3" = Table.AddColumn(#"Removed Columns1", "Custom.2", each Table.AddColumn([Custom], "Aux", each List.PositionOf({"C","E","D","B","A","K","F"}, [Column1]))),
    #"Added Custom4" = Table.AddColumn(#"Added Custom3", "Custom.3", each Table.Sort([Custom.2], each [Aux])),
    #"Added Custom6" = Table.AddColumn(#"Added Custom4", "Custom.4", each Text.Combine([Custom.3][Column1],"|")),
    #"Removed Columns2" = Table.RemoveColumns(#"Added Custom6",{"Custom", "Custom.2", "Custom.3"})
in
    #"Removed Columns2"

View solution in original post

3 REPLIES 3
AlB
Super User
Super User

 

I thought this, a small variation of what we had before, would work but it doesn't. I haven't had the time to fix it:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("PczBDQAgCAPAVQxvl0BhCuImHd4q6A/K0QhRGEaTzmHAZPWQCYXDMpy8+43LPUUBTfPFWa2ws4/vr7p61gY=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Series " = _t, ExpectedOutput = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Series ", type text}, {"ExpectedOutput", type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"ExpectedOutput"}),
    #"Trimmed Text" = Table.TransformColumns(#"Removed Columns",{{"Series ", Text.Trim, type text}}),
    #"Added Custom" = Table.AddColumn(#"Trimmed Text", "Custom", each Text.Combine(List.Sort(Text.Split([#"Series "],"|"),  List.PositionOf({"C","E","D","B","A","K","F"}, [#"Series "])),"|")),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Custom",{"Series "})
in
    #"Removed Columns1"

So try this other approach instead, less elegant but effective: 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("PczBDQAgCAPAVQxvl0BhCuImHd4q6A/K0QhRGEaTzmHAZPWQCYXDMpy8+43LPUUBTfPFWa2ws4/vr7p61gY=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Series " = _t, ExpectedOutput = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Series ", type text}, {"ExpectedOutput", type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"ExpectedOutput"}),
    #"Trimmed Text" = Table.TransformColumns(#"Removed Columns",{{"Series ", Text.Trim, type text}}),
    #"Added Custom" = Table.AddColumn(#"Trimmed Text", "Custom", each Table.FromList(Text.Split([#"Series "],"|"))),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Custom",{"Series "}),
    #"Added Custom3" = Table.AddColumn(#"Removed Columns1", "Custom.2", each Table.AddColumn([Custom], "Aux", each List.PositionOf({"C","E","D","B","A","K","F"}, [Column1]))),
    #"Added Custom4" = Table.AddColumn(#"Added Custom3", "Custom.3", each Table.Sort([Custom.2], each [Aux])),
    #"Added Custom6" = Table.AddColumn(#"Added Custom4", "Custom.4", each Text.Combine([Custom.3][Column1],"|")),
    #"Removed Columns2" = Table.RemoveColumns(#"Added Custom6",{"Custom", "Custom.2", "Custom.3"})
in
    #"Removed Columns2"
AlB
Super User
Super User

Hi @Anonymous 

Try this based on your sample data. The important part is the one highlighted in red:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("PczBDQAgCAPAVQxvl0BhCuImHd4q6A/K0QhRGEaTzmHAZPWQCYXDMpy8+43LPUUBTfPFWa2ws4/vr7p61gY=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Series " = _t, ExpectedOutput = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Series ", type text}, {"ExpectedOutput", type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"ExpectedOutput"}),
    #"Added Custom" = Table.AddColumn(#"Removed Columns", "Custom", each Text.Combine(List.Sort(Text.Split([#"Series "],"|"),Order.Ascending),"|")),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Custom",{"Series "})
in
    #"Removed Columns1"

 

Anonymous
Not applicable

@AlB - Thanks for the excellent code. This perfectly works.can i add one more qn?  In the real case, the order is not simple ascending, can we sort this based on a custom list or order? For Ex: Instead of A|B|C|D|E|F|K, the order should be C|E|D|B|A|K|F, how to do that?

 

Many thanks for your help.

 

Thanks
Raj

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.