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
Solved! Go to 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
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,
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
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,
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}}, "enBS"),
#"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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingCan 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)
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingI 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.
Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.
Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!
User  Count 

135  
24  
20  
13  
11 
User  Count 

148  
38  
31  
18  
17 