Forum Discussion
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 is {A1, A2, ... An}as well as {B1 ... Bn} and {C1 ... Cn} (otherwise that would be too simple).
I would like to extract the 3 list items in separate column, but I don't want As mixed with Bs and Cs.
What's the most efficient way to go about cleaning this up?
6 Replies
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?
Hi benjamin_sasin ,
Kindly share your sample data and excepted result to me if you don't have any Confidential Information. Please upload your files to One Drive and share the link here.
The data is fairly simple:
"2019","Taipei","Attended"
"Not Attended", "2018", "Hong Kong"
"Jakarta", "Attended", "2018"
etc.
Since there is a limited number of possible statuses, I've extracted these first with:
= Table.AddColumn(#"Replaced Value4", "AttendanceStatus", each if Text.Contains([TAGS],"Not Attended") then "Not Attended" else if Text.Contains([TAGS],"Attended") then "Attended" else "Scanned")
Then removed these with a replace value.
Then I did the same for the year, which is limited to 2019 and 2018 anyway.
After that I cleaned up all the extra quotes and commas.
I don't think there's a more efficient way after looking into this.
Hi,
Would extract text before/after delimiter work if you can find a similarity between the "A" "B" and "C's" ?
