Forum Discussion

benjamin_sasin's avatar
benjamin_sasin
Resolver I
6 years ago
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...
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    Exactly! 

     

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

v-frfei-msft's avatar
v-frfei-msft
Community Support
6 years ago

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's avatar
benjamin_sasin
Resolver I
6 years ago

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.