Power Query TextJoin

[Maviso69]

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

[Maviso69]

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

[AntrikshSharma]

@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]

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

[AntrikshSharma · Accepted solution]

@Maviso69 You need to go to advanced editor and then use the GroupedRows step/code, I have uploaded the file here https://drive.google.com/file/d/1M6ULVsU3m_IJc08CupGCwbJXCcVyGPDe/view?usp=share_link to give you an idea.

[Maviso69]

Many thanks - much appreciated.