Reordering a list

[benjamin_sasin]

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?

[Anonymous]

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"​

[benjamin_sasin]

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?

[Anonymous · Accepted solution]

Exactly! 

And to your point, I could probably have used List.Intersect instead of List.Select, List.Contains.

[nullpowerbi]

Hi,

Would extract text before/after delimiter work if you can find a similarity between the "A" "B" and "C's" ?

[v-frfei-msft]

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.

[benjamin_sasin]

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.