Forum Discussion

savannahr's avatar
savannahr
Regular Visitor
2 years ago
Solved

Separate data and Count

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? 

Need Help

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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.