Table.Group by different criteria in the same table

[ValeriaBreve]

Hi all

I need to group my table as per below example:

So by Product, summing the amount, concatenating the Product Code and Type IF DIFFERENT ONLY, and then getting the MAX date.

Now I could only do a part of it:

#"Grouped Rows" = Table.Group(#"Changed Type", {"Product"}, {{"Amount", each List.Sum([Amount])},{"Max Update Date", each List.Max([Date])},{"Type", each Text.Combine([Type], ", "), type text}})

which leads me to:

now I am still missing:

1) How can I tell PowerQuery to only concatenate if the text is different?

2) How can I work with concatenating the Product Type, which is a number? I tried to use Number.ToText but every time I get the error "We cannot convert a value of type List to type Number".  Can somebody please explain to me why this happens?

Thank you very much in advance for your help!

Kind regards

Valeria

[v-stephen-msft]

Hi @ValeriaBreve ,

Could you please tell me that if your problems have been solved?

If so, please mark the helpful replies as solution. More people will benefit from them.

Best Regards,

Stephen Tao

[ValeriaBreve]

of course and sorry for th delay- I was out of office. Here actually there are 2 posts that are a solution for me - I guess I can't mark them both as solutions (but tell me if I am wrong) - I will go with the first one answered....

[AntrikshSharma]

@ValeriaBreve You can use this:

let
    Source = Table.FromRows (
        Json.Document (
            Binary.Decompress (
                Binary.FromText (
                    "i45WCijKTylNLnFU0lEyNQIShiBsoG8EQkYgAafM7NRipVgduFInoKCZMZAwQlcaklGUmpiCrNYZKGppAiSM0dU65+SXZGTmpSOrBjnCzAJEQFWb4DYZ5ApTMyRX4FHrDDUSQynCEbEA",
                    BinaryEncoding.Base64
                ),
                Compression.Deflate
            )
        ),
        let
            _t = ( ( type nullable text ) meta [ Serialized.Text = true ] )
        in
            type table [ Product = _t, Amount = _t, #"Product Code" = _t, Date = _t, Type = _t ]
    ),
    ChangedType = Table.TransformColumnTypes (
        Source,
        {
            { "Product", type text },
            { "Amount", Int64.Type },
            { "Product Code", type text },
            { "Date", type date },
            { "Type", type text }
        }
    ),
    Group = Table.Group (
        ChangedType,
        { "Product" },
        {
            {
                "Count",
                ( Rows ) =>
                    let
                        TotalAmount = List.Sum ( Rows[Amount] ),
                        ProductCode = 
                            Text.Combine ( 
                                List.Distinct ( Rows[Product Code] ), 
                                ";" 
                            ),
                        MaxDate = List.Max ( Rows[Date] ),
                        Type = Text.Combine ( List.Distinct ( Rows[Type] ), ";" ),
                        Result = [
                            Amount       = TotalAmount,
                            Product Code = ProductCode,
                            Date         = MaxDate,
                            Type         = Type
                        ]
                    in
                        Result
            }
        }
    ),
    ExpandedCount = Table.ExpandRecordColumn (
        Group,
        "Count",
        { "Amount", "Product Code", "Date", "Type" },
        { "Amount", "Product Code", "Date", "Type" }
    ),
    ChangedType2 = Table.TransformColumnTypes (
        ExpandedCount,
        {
            { "Amount", Currency.Type },
            { "Product Code", type text },
            { "Date", type date },
            { "Type", type text }
        }
    )
in
    ChangedType2

[ppm1 · Accepted solution]

Try wrapping the column reference inside your Text.Combine with List.Distinct like this:

Text.Combine(List.Distinct([Type]), ", ")

Pat

[ValeriaBreve]

Hello, this does not work in my query, it still gives me the error: 

Expression.Error: We cannot convert the value 1 to type Text.
Details:
Value=1
Type=[Type]

[ValeriaBreve]

It works great! Very efficient!!!! Thank you 🙂 I will wait to mark the post as solved because I am still missing question 2...

[latimeria]

Try this for question 2:

Text.Combine(List.Transform(List.Distinct([Product Code]), Text.From), ",")

[ValeriaBreve]

works very nicely - thank you!