Forum Discussion
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
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
8 Replies
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])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,
- Original data
-
- Group by CompteNum and CompteLib, summarize Montant
-
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.- Original data
Thank a lot,
I try to understand and change my code as soon as possible.
Philippe Muniesa
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
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 ForumAlso, if you could mock up what you think it should look like in Excel and share that (for that a pic is fine)
