Forum Discussion

DeBIe's avatar
DeBIe
Icon for Post Partisan rankPost Partisan
6 years ago
Solved

Need help merging/combining values in a column

Hi All,

 

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

 

ColumnGamesQuantity

Call of Duty

2

Call of Duty (old)4
Battlefield2
Battlefield (old)6
Sims3
Sims (Old)3

 

My desired output column would look like this:

 

ColumnGamesQuantity
Call of Duty Total6
Battlefield Total8
Sims Total6
  

 

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

Need Help
  • v-xicai's avatar
    v-xicai
    6 years ago

    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.

4 Replies

  • v-xicai's avatar
    v-xicai
    Icon for Community Support rankCommunity Support
    6 years ago

    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's avatar
      DeBIe
      Icon for Post Partisan rankPost Partisan
      6 years ago

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

  • BA_Pete's avatar
    BA_Pete
    Icon for Super User rankSuper User
    6 years ago

    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's avatar
      parry2k
      Icon for Super User rankSuper User
      6 years ago

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