Need help merging/combining values in a column

[DeBIe]

Hi All,

I would like to merge/combine 100+ values from a column. At the moment my column looks like this:

ColumnGames	Quantity
Call of Duty	2
Call of Duty (old)	4
Battlefield	2
Battlefield (old)	6
Sims	3
Sims (Old)	3

My desired output column would look like this:

ColumnGames	Quantity
Call of Duty Total	6
Battlefield Total	8
Sims Total	6

I hope someone can help me with this. Thanks in advance!

[v-xicai · Accepted solution]

Hi @DeBIe ,

As parry2k's suggestion, you may enter into Query Editor, select on the column [ColumnGames] , right click to choose option "Replace Values", set like below, click button "Close & Apply". Then you may use Table visual to display the two fields, which has a Sum aggregation for the [Quantity].

Best Regards,

Amy 

Community Support Team _ Amy

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

[DeBIe]

Thank you all for your time and feedback. @v-xicai your solution worked out best for me. Also, the easiest to implement.

[BA_Pete]

Hi @DeBIe ,

You can do this in Power Query as follows:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wck7MyVHIT1NwKS2pVNJRMlKK1UEVVNDIz0nRBEqZgKWcEktKclLTMlNzUuDKkcTgqs3AMsGZucVAjjGco6DhD5EHCsUCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ColumnGames = _t, Quantity = _t]),
    chgAllTypes = Table.TransformColumnTypes(Source,{{"ColumnGames", type text}, {"Quantity", Int64.Type}}),
    splitByBracket = Table.SplitColumn(chgAllTypes, "ColumnGames", Splitter.SplitTextByEachDelimiter({"("}, QuoteStyle.Csv, false), {"ColumnGames", "ColumnGames.2"}),
    remSplit2 = Table.RemoveColumns(splitByBracket,{"ColumnGames.2"}),
    trimColumnGames = Table.TransformColumns(remSplit2,{{"ColumnGames", Text.Trim, type text}}),
    groupColumnGames = Table.Group(trimColumnGames, {"ColumnGames"}, {{"Quantity", each List.Sum([Quantity]), type number}})
in
    groupColumnGames

In Power Query, go to New Source > Blank Query, then go to Advanced Editor and paste in my code above over the default query code. You can then follow the steps I took to complete this.

I have assumed that the only difference between names is the addition of "(Old)" in all cases, as I've split your [columnGames] based on the "(" portion of it.

Pete

[parry2k]

@DeBIe in power query, replace (old) with "" (no space) and this will group all games together. just close and apply and in visual you can now use sum by columngames

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡