Forum Discussion
removing duplicated rows without losing information
- 2 years ago
George_Gian last 2 steps must be
#"Group" = Table.Group(#"Sorted Rows",{"Product"},{{"x",(x)=>{Table.FirstValue(x)} & x[Market]}})[[x]], #"To_table" = Table.FromColumns(List.Zip(#"Group"[x]))
George_Gian last 2 steps must be
#"Group" = Table.Group(#"Sorted Rows",{"Product"},{{"x",(x)=>{Table.FirstValue(x)} & x[Market]}})[[x]],
#"To_table" = Table.FromColumns(List.Zip(#"Group"[x]))hi AlienSx ,thank you again for helping me out.
i try to understand the M functions you used (part of my learning journey :-)) and i am stuck with this entry
{{"x",(x)=>{Table.FirstValue(x)} & x[Market]}})[[x]]of the Table.Group function.
According to Microsoft, the syntax of this function is :
Table.Group(table as table, key as any, aggregatedColumns as list, optional groupKind as nullable number, optional comparer as nullable function) as table
,so unless if i am mistaken, the part that i asked more info on is the aggregated columns as list.
I just dont understand how this code works at this specific point.
Any help to demystify this would be utterly welcomed 🙂
- AlienSx2 years agoSuper User
hello, George_Gian aggregatedColumns argument looks like this:
{ {"column_name_01", aggregation_function_01}, {"column_name_02", aggregation_function_02}, ..., {"column_name_N", aggregation_function_N} }So it's a list of lists. Each nested list consists of column name and function (or aggregation function). Table.Group passes single argument to this function - the table made by original table grouping. Aggregated function must do something (or nothing - up to you) to this table and land the result into "column name" column. Try to create new columns yourself using different table functions like Table.Skip, Table.RemoveFirstN, Table.Max etc. or your own custom functions.
As mentioned earlier, aggregation function is always a function of single argument (table). So it must be defined as
(x) => ... or (any_other_single_variable_name) => ... or (_) => ... or each ... (which is equivalent of (_) => ...). If applied library table function or your own custom function is a function of single argument then explicit variable definition may be ommited like this: {"row count", Table.RowCount}
Read more about Table.Group arguments on Rick de Groot's powerquery.how web site.
- George_Gian2 years agoRegular Visitor
Thank you AlienSx for this explanation and for the link.
to be honest, I have started to findMicrosoft's online material not that helpful, they tend to complicate things, but maybe that's just me 🙂
thank you again
- AlienSx2 years agoSuper User
I would also recommend to consider this book (on the first page of Rick's web site): Definitive Guide to Power Query (M). I don't have it but it's table of contents looks promising. Web site itself is also very helpful.