Cleanse Data

[Anonymous]

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: Dog Alpha {LINK id=17272 uri=Dog} 17273: Cat Beta {LINK id=17273 uri=Cat} 17275: Rabbit Beta {LINK id=17275 uri=Rabbit}
3	17272: Dog Alpha {LINK id=17272 uri=Dog} 17275: Rabbit Beta {LINK id=17275 uri=Rabbit}
4	17275: Rabbit Beta {LINK id=17275 uri=Rabbit}

And I would like it to look like this;

Name	Type
1	Dog Alpha
2	Dog Alpha Cat Beta Rabbit Beta
3	Dog Alpha Rabbit Beta
4	Rabbit Beta

Thanks

[Greg_Deckler]

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]

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]

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]

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.