Forum Discussion

ValeriaBreve's avatar
ValeriaBreve
Post Partisan
3 years ago
Solved

Table.Group by different criteria in the same table

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

 

 

 

Need Help
  • ppm1's avatar
    ppm1
    3 years ago

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

     

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

     

    Pat

8 Replies

  • ppm1's avatar
    ppm1
    Solution Sage
    3 years ago

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

     

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

     

    Pat

    • ValeriaBreve's avatar
      ValeriaBreve
      Post Partisan
      3 years ago

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

      • latimeria's avatar
        latimeria
        Solution Specialist
        3 years ago

        Try this for question 2:

        Text.Combine(List.Transform(List.Distinct([Product Code]), Text.From), ",")
    • ValeriaBreve's avatar
      ValeriaBreve
      Post Partisan
      3 years ago

      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]

  • AntrikshSharma's avatar
    AntrikshSharma
    Community Champion
    3 years ago

    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

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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's avatar
      ValeriaBreve
      Post Partisan
      3 years ago

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