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.