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
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.