Forum Discussion
Solved
Reordering a list
I've got a data-set with a column containing a list like "A","B","C" and sometimes that column also has "B","C","A" or other sequence (here A, B and C represent string values). Naturally, there i...
Exactly!
And to your point, I could probably have used List.Intersect instead of List.Select, List.Contains.
Does this work for you?
let
Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Line", type text}}),
#"Added Custom1" = Table.AddColumn(#"Changed Type", "LineList", each Text.Split([Line],",")),
#"Add AList" = Table.AddColumn(#"Added Custom1", "AList", each Text.Combine(
List.Select([LineList], each List.Contains({"a1","a2","a3"},_))
, ",")),
#"Add BList" = Table.AddColumn(#"Add AList", "BList", each Text.Combine(
List.Select([LineList], each List.Contains({"b1","b2","b3"},_))
, ",")),
#"Add CList" = Table.AddColumn(#"Add BList", "CList", each Text.Combine(
List.Select([LineList], each List.Contains({"c1","c2","c3", "c4","c5"},_))
, ",")),
#"Removed Columns" = Table.RemoveColumns(#"Add CList",{"LineList"})
in
#"Removed Columns"
I like that.
So you transform the string into a list, which becomes the value of a new column.
Then you create three columns and for each column you intersect the value of the list with the standard list for that column?
Exactly!
And to your point, I could probably have used List.Intersect instead of List.Select, List.Contains.