Forum Discussion
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 co...
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"
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"