Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago

Cleanse Data

Hi been looking at cleaning up some data but not to sure what is the best approach to this;

 

The data I have is below;

NameType
117272: Dog Alpha {LINK id=17272 uri=Dog}
217272: Dog Alpha {LINK id=17272 uri=Dog}
17273: Cat Beta {LINK id=17273 uri=Cat}
17275: Rabbit Beta {LINK id=17275 uri=Rabbit}
317272: Dog Alpha {LINK id=17272 uri=Dog}
17275: Rabbit Beta {LINK id=17275 uri=Rabbit}
417275: Rabbit Beta {LINK id=17275 uri=Rabbit}

 

And I would like it to look like this;

 

NameType
1Dog Alpha
2Dog Alpha
Cat Beta
Rabbit Beta
3Dog Alpha
Rabbit Beta
4Rabbit Beta

 

Thanks

Need Help

4 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    8 years ago

    One method would be to do a split at the colon-space (: ). Throw away the left column. Or, perhaps essentially do a right throwing away the leftmost 7 characters.

     

    Taking the resulting column, do a split on space character. Keep the 2 leftmost colums and throw everything else away. Concatenate back to a single column.

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      Hi Greg,

       

      Thanks for your help, I have taken this method and it works how I would like it to but when I do the merge back, I use the comma to split the data up but it gives me trailering comms.

       

      Like below;

       

      Merge
      Dog Alpha,,,
      Cat Beta,,,
      Dog Alpha,,,
      Cat Beta,Dog Alpha,,
      Cat Beta,Dog Alpha,Rabbit Beta,

       

      What would be the best method to remove these end commas?

       

      Also in the future what happens if say they put more then 3 items in the section?

       

      Thanks

      • Greg_Deckler's avatar
        Greg_Deckler
        Community Champion
        8 years ago

        Have a look at this:

         

        let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTI0NzI3slJwyU9XcMwpyEhUqPbx9PNWyEyxBcsolBZl2gIla5VidaKVjEjVAFVvbKXgnFii4JRagqbcGKwcKIei3NRKISgxKSkTmw5TsA6INESTMZmOIs0WE9J1xQIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Name = _t, Type = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", Int64.Type}, {"Type", type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Type", Splitter.SplitTextByEachDelimiter({": "}, QuoteStyle.Csv, false), {"Type.1", "Type.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Type.1", Int64.Type}, {"Type.2", type text}}),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Changed Type1", "Type.2", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Type.2.1", "Type.2.2", "Type.2.3", "Type.2.4", "Type.2.5"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Type.2.1", type text}, {"Type.2.2", type text}, {"Type.2.3", type text}, {"Type.2.4", type text}, {"Type.2.5", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type2", "Custom", each [Type.2.1] & " " & [Type.2.2]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Type.1", "Type.2.1", "Type.2.2", "Type.2.3", "Type.2.4", "Type.2.5"})
in
    #"Removed Columns"