cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
PhilippeMuniesa
Helper III
Helper III

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

My problem is that the names of the columns as well as their number is variable.

 

The original table is an excerpt from an accounting transaction log.

JournalCode

CompteNum

CompteLib

PieceRef

PieceDate

EcritureLib

Montant

VE

4110000001

CLT COLLECTIF

VAJYAAAA

09/01/2019

CRECHE ATTITUDE 1T19 - FC00326

317795

VE

4457100000

TVA collectée

VAJYAAAA

09/01/2019

CRECHE ATTITUDE 1T19 - FC00326

-529658

VE

7060400000

Loyer crèche

VAJYAAAA

09/01/2019

CRECHE ATTITUDE 1T19 - FC00326

-2648292

VE

4110000001

CLT COLLECTIF

VAJYAAAB

20/01/2019

RESTAURATION COLLECTIVE 1T19 -

124187

VE

4457100000

TVA collectée

VAJYAAAB

20/01/2019

RESTAURATION COLLECTIVE 1T19 -

-206978

VE

7060300000

Loyer RIE

VAJYAAAB

20/01/2019

RESTAURATION COLLECTIVE 1T19 -

-1034892

VE

4110000001

CLT COLLECTIF

VAJYAAAC

31/03/2019

SCI ANTIPOLE - FC003270

12224

VE

4457100000

TVA collectée

VAJYAAAC

31/03/2019

SCI ANTIPOLE - FC003270

-2037

VE

7060100000

Cotisations HT

VAJYAAAC

31/03/2019

SCI ANTIPOLE - FC003270

-10187

etc.....

 

First, The "CompteNum" column has been rotated with as value, the content of the column "Montant"

 

SourceTable = Table.Pivot(Source, List.Distinct(Source[CompteNum]), "CompteNum", "Montant", List.Sum),

 

I Get a new table  named "SourceTable"

 

JournalCode

CompteLib

PieceRef

PieceDate

EcritureLib

4110000001

4457100000

7060400000

7060300000

7060100000

VE

TVA collectée

VAJYAAAA

09/01/2019

CRECHE ATTITUDE 1T19 - FC00326

 

-529658

 

 

 

VE

Loyer crèche

VAJYAAAA

09/01/2019

CRECHE ATTITUDE 1T19 - FC00326

 

 

-2648292

 

 

VE

CLT COLLECTIF

VAJYAAAA

09/01/2019

CRECHE ATTITUDE 1T19 - FC00326

317795

 

 

 

 

VE

CLT COLLECTIF

VAJYAAAB

20/01/2019

RESTAURATION COLLECTIVE 1T19 -

124187

 

 

 

 

VE

TVA collectée

VAJYAAAB

20/01/2019

RESTAURATION COLLECTIVE 1T19 -

 

-206978

 

 

 

VE

Loyer RIE

VAJYAAAB

20/01/2019

RESTAURATION COLLECTIVE 1T19 -

 

 

 

-1034892

 

VE

TVA collectée

VAJYAAAC

31/03/2019

SCI ANTIPOLE - FC003270

 

-2037

 

 

 

etc...

 

To find the name of the column to Group, I created a list "DistinctValue" from an example provided on this forum.

 

#"Converted to Table" = Table.FromList(Custom2, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Added Index" = Table.AddIndexColumn(#"Converted to Table", "Index", 0, 1),
#"Filtered Rows" = Table.SelectRows(#"Added Index", each [Index] > 4),

 

I Get  DistinctValue  List

 

Liste

4110000001
4457100000
7060400000
7060300000
7060100000

 

 

However after I stay blocked on the use of the Total Group function that must use the Distinct Value list.

 

I've done :

Agregation = Table.Group (SourceTable, "PieceRef"" - List.Transform (DistinctValue , each , List.Sum, "sum")) ... that doesn't work,

 

I've also tried

Agregation = Table.Group(SourceTable, {"PieceRef"}, List.Transform(DistinctValue , each {_, List.Sum, _&"sum" }))

 

or

Agregation = Table.Group (Source as table, "PieceRef", DistinctValue) as table

 

But still without success.

 

Thank you for your help and excuse me for my poor google english traduction....  😞

PS: I do not see how deposit my sample Excel file.

 

Philippe Muniesa

1 ACCEPTED SOLUTION

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




Feel free to connect with me:
LinkedIn

View solution in original post

8 REPLIES 8
Smauro
Solution Sage
Solution Sage

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

 




Feel free to connect with me:
LinkedIn

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

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




Feel free to connect with me:
LinkedIn

Thanks a lot

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

 

philippe muniesa

Thank a lot,

 

I try to understand and change my code as soon as possible.

 

Philippe Muniesa

@Smauro thanks for the data. I did try Excel and it was the same single column of data. Dunno why..

 

That said, @PhilippeMuniesa can you explain what your expected result is? Like @Smauro said, you shouldn't unpivot. Just grab the Compteum column and group, or grab a few columns. For example, 

  1. Original data
    1. edhans_0-1594658333345.png

       

  2. Group by CompteNum and CompteLib, summarize Montant
    1. edhans_1-1594658393114.png

       

If that doesn't help, can you please post what you expect the results to be? In general, the pivot and unpivot functions are use to normalize data, not denormalize it, and your data looks normalized to me, which is good.

The M code for the grouping I did is as follows:

let
    Source = 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]),
    #"Changed Type with Locale" = Table.TransformColumnTypes(Source, {{"PieceDate", type date}}, "en-BS"),
    #"Changed Type" = Table.TransformColumnTypes(#"Changed Type with Locale",{{"Montant", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"CompteNum", "CompteLib"}, {{"Total", each List.Sum([Montant]), type number}})
in
    #"Grouped Rows"

 

1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
edhans
Super User
Super User

Can you either repost your data using the links below to properly share table data, or share an excel/PBIX file via OneDrive or other file sharing service? When I tried to copy your table, I got one massive column which I cannot work with.

 

How to get good help fast. Help us help you.
How to Get Your Question Answered Quickly
How to provide sample data in the Power BI Forum

 

Also, if you could mock up what you think it should look like in Excel and share that (for that a pic is fine)

 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
Rocco_sprmnt21
Community Champion
Community Champion

I have not been able to understand what the expected result is.
You should show an input table and a table with the expected result, to facilitate understanding.
Furthermore, a verbal description of the various input structures that should eventually be managed would be useful.

Helpful resources

Announcements
Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

May UG Leader Call Carousel 768x460.png

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!