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; Name Type 1 17272: Dog Alpha {LINK id=17272 uri=Dog} 2 17272: Do...
Need Help
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"