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"
  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    Hi Greg,

     

    Thanks for looking into this for me, I have looked into this and it seems that the code you have shown only brings back the first result in the column when there could be two or more results as it is a 1:N column.

     

    Also by using this method you are left with the problem that I thought of before which is when I create this report when they do a refresh if the data then has five items in the column it will be broken?

     

    Is there another method to do this in a loop maybe?

     

    Thanks for all your help.