Forum Discussion

Maviso69's avatar
Maviso69
New Member
3 years ago
Solved

Power Query TextJoin

I import a sorted two column list in power query.

Column A contains the product codes
Coumn B contains a category that the product code belongs to.

A given product code will belong to several categories and therefore will appear on a number of rows - each time against a category.

I wuold like to transfom this a follows:

Column A (doesn't have to be) contains a row for each unique product code.
Column B (doesn't have to be) now contains a comma / pipe seperated entry containing all the categories that the product belongs to.

Can this be done? - Yes you guessed I'm a Newbie.

Malcolm

Need Help

5 Replies

  • AntrikshSharma's avatar
    AntrikshSharma
    Community Champion
    3 years ago

    Maviso69 Create a new blank query in Power Query UI then go to Advanced Editor and paste this code:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUXIsKMhJVYrViVZyAvJ8E/PS88E8ZxQeSKVTYh4Q4lAakJpYBFfpnJFaVFQJVxmeWJJalJuak5+HpjwWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Code = _t, Category = _t]),
    ChangedType = 
        Table.TransformColumnTypes ( 
            Source, 
            { 
                { "Code", type text }, 
                { "Category", type text }
            }
        ),
    GroupedRows = 
        Table.Group ( 
            ChangedType, 
            {"Code"}, 
            {
                {
                    "Result", 
                    each 
                        Text.Combine ( 
                            List.Distinct ( _[Category] ), 
                            ", " 
                        ), 
                    type text
                }
            }
        )
in
    GroupedRows

     

    • Maviso69's avatar
      Maviso69
      New Member
      3 years ago

      Apologies for my naivity but trying this out "as is" prom

      I'm guessing this part needs to be edited by me according to my system? My "source" is "Table1".....

      let
      Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUXIsKMhJVYrViVZyAvJ8E/PS88E8ZxQeSKVTYh4Q4lAakJpYBFfpnJFaVFQJVxmeWJJalJuak5+HpjwWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Code = _t, Category = _t]),
      ChangedType =
      Table.TransformColumnTypes (
      Source,
      {
      { "Code", type text },
      { "Category", type text }
      }
      ),

      result Error message - "Eof expected"

       

      Further advice would be appreciated - Malcoln

  • Maviso69's avatar
    Maviso69
    New Member
    3 years ago

    Wow! - way beyond my programming ability at this point so will happily cut & paste to try. Intersting that your code doesn't appear to require "Column A" to be sorted either. Will try in the morning now. Many thanks