Forum Discussion

PhilippeMuniesa's avatar
PhilippeMuniesa
Icon for Resolver I rankResolver I
6 years ago
Solved

Dynamic Table.group

Hello,   I am a French user, and not find a lot of  documentation or examples in my language.   I'm looking to group (with sum agregation)  several columns of a table, by grouping with the Table....
Need Help
  • Smauro's avatar
    Smauro
    6 years ago

    I'll try to break it down:

    List.Transform(List.Distinct(PreviousStep[CompteNum]), (t) => {Text.From(t), each List.Sum(Table.SelectRows(_, each [CompteNum] = t)[Montant]), type number})

    to

    List.Transform(
    List.Distinct(PreviousStep[CompteNum]),
    (t) =>
        {
            Text.From(t),
            each
                List.Sum(
                    Table.SelectRows(
                        _,
                        each [CompteNum] = t
                    )
                        [Montant]
                ),
            type number
        }
)

    List.Transform takes two arguments: 1) a list and 2) a transformation function. Then, for each of its elements, it applies the transformation function.

    List.Distinct ( PreviousStep[CompteNum] ) gives a list of distinct elements in [CompteNum].

    (t) => ... is basically the transformation function defined. It takes one argument and returns a list in our case.


    I'll give an example:

    List.Transform(
    List.Transform({"a", "b"}, 
    (t) =>
        {
            Text.From(t),
            each
                List.Sum(
                    Table.SelectRows(
                        _,
                        each [CompteNum] = t
                    )
                        [Montant]
                ),
            type number
        }
)​

    will return:

    {
    {
        "a",
        each
            List.Sum(
                Table.SelectRows(
                    _,
                    each [CompteNum] = "a"
                )
                    [Montant]
            ),
        type number
    },
    {
        "b",
        each
            List.Sum(
                Table.SelectRows(
                    _,
                    each [CompteNum] = "b"
                )
                    [Montant]
            ),
        type number
    }
}


    Which is in turn passed as the third argument in Table.Group. I hope it already looks to you like Table.Group's third argument, which, when executed, every _ will be traslated as the grouped table based on the grouping criteria.

     

     

    Best,

    Spyros

Smauro's avatar
Smauro
Icon for Solution Sage rankSolution Sage
6 years ago

Hi PhilippeMuniesa ,

 

Assuming I understood what you'd like to do, you could skip pivoting:

 

 

    #"Group by PieceRef" = Table.Group(PreviousStep, {"PieceRef"}, List.Transform(List.Distinct(PreviousStep[CompteNum]), (t) => {Text.From(t), each List.Sum(Table.SelectRows(_, each [CompteNum] = t)[Montant]), type number}))

 

 

Where PreviousStep is your last query step.

 

Beware that you're going to lose all other columns. If you want to keep them, you should add them in the grouping columns; the {"PieceRef"} part.

 

Cheers

 

edhansI had the same issue, but pasting the table first in excel and copying it again to paste it in PBI's "Enter Data" is the quickest way to fix that. Excel is smarter when pasting something that looks like a table.

Here is the table in case you'd like to try something:

Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("pZJBasMwEEWvIryO8YwkW9ZSVRSiYuLiKIYSsijGkECoIfGm616mvUZ7saoudpOu4npWM/Clh95ouw1KE8wCjghdoR905ojOs8xoZxd+LtX9o/LlW5ARYEQB5XeuMHppiHLOus3cEHQoSUgWGoDRxAcYCiHjYDfrKTwWPxw/uFKRqjke66r9fP14r6eAwpjKJE5/SQIS4D0pa17qE6lOHvNW7aeBaMJTKunFm24wd+dbChecwqyd2hTK2Xw1HCh7ng8g5ZiK0ebGg0IKiRR/zLFrc4U1kxAIjKdjnenu/0TAes5aW6JWzj7kmRl2IqCTRSkf7WoEwEti4lrRANBNezg/tYfm+UyW7n/XI3TL3n0B", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [JournalCode = _t, CompteNum = _t, CompteLib = _t, PieceRef = _t, PieceDate = _t, EcritureLib = _t, Montant = _t])

 

PhilippeMuniesa's avatar
PhilippeMuniesa
Icon for Resolver I rankResolver I
6 years ago

Smauro,

 

 

Thank you very much, it works.

The second solution is too complex for my level.

I keep your first example. Would you be kind to explain to me how the second part works.

I understand that this is a function that uses a t setting. This setting is turned into text. But, I don't understand what t and where it comes from.

 

Thank you in advance.

 

Philippe Muniesa

  • Smauro's avatar
    Smauro
    Icon for Solution Sage rankSolution Sage
    6 years ago

    I'll try to break it down:

    List.Transform(List.Distinct(PreviousStep[CompteNum]), (t) => {Text.From(t), each List.Sum(Table.SelectRows(_, each [CompteNum] = t)[Montant]), type number})

    to

    List.Transform(
    List.Distinct(PreviousStep[CompteNum]),
    (t) =>
        {
            Text.From(t),
            each
                List.Sum(
                    Table.SelectRows(
                        _,
                        each [CompteNum] = t
                    )
                        [Montant]
                ),
            type number
        }
)

    List.Transform takes two arguments: 1) a list and 2) a transformation function. Then, for each of its elements, it applies the transformation function.

    List.Distinct ( PreviousStep[CompteNum] ) gives a list of distinct elements in [CompteNum].

    (t) => ... is basically the transformation function defined. It takes one argument and returns a list in our case.


    I'll give an example:

    List.Transform(
    List.Transform({"a", "b"}, 
    (t) =>
        {
            Text.From(t),
            each
                List.Sum(
                    Table.SelectRows(
                        _,
                        each [CompteNum] = t
                    )
                        [Montant]
                ),
            type number
        }
)​

    will return:

    {
    {
        "a",
        each
            List.Sum(
                Table.SelectRows(
                    _,
                    each [CompteNum] = "a"
                )
                    [Montant]
            ),
        type number
    },
    {
        "b",
        each
            List.Sum(
                Table.SelectRows(
                    _,
                    each [CompteNum] = "b"
                )
                    [Montant]
            ),
        type number
    }
}


    Which is in turn passed as the third argument in Table.Group. I hope it already looks to you like Table.Group's third argument, which, when executed, every _ will be traslated as the grouped table based on the grouping criteria.

     

     

    Best,

    Spyros

    • PhilippeMuniesa's avatar
      PhilippeMuniesa
      Icon for Resolver I rankResolver I
      6 years ago

      Thanks a lot

      I examine that step by step and try to produce with other table

       

      philippe muniesa