Separate data and Count

[savannahr]

So i am newer to using powerbi, heavy excel user and trying to shift so please i know this may be a novice thing but i need help doing in powerbi. I have a report which is joining data with a + and things are listed multiple times example below. I am then counting how many times this appears in the data. See example below for more context. I need to seperate it out and still keep the counts accurate. Each line of data has either the single data point or multiples and i want to change it out make all clean single items displayed. 

How it is now, example

Red+Blue l 1,000

Red l 400

Blue l 500

How i want to display

Red l 1,400

Blue l 1,500

I cannot think how to search and do this people are showing with names not the same and not accounting for totals. Can someone give me guidence how to do this? 

[Anonymous]

Hi @savannahr 

You can put the following code to advanced editor in power query

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCkpN0XbKKU1V0lEyNDAwUIrVAYsBuSZQHlTWFMSNBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Changed Type", {{"Column1", Splitter.SplitTextByDelimiter("+", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Column1"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Column1", type text}, {"Column2", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type1", {"Column1"}, {{"Sum", each List.Sum([Column2]), type nullable number}})
in
    #"Grouped Rows"

Output

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ryan_mayu · Accepted solution]

@savannahr 

try to split the column in pq