Forum Discussion
removing duplicated rows without losing information
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]))
Hello AlienSx , thank you for your reply.
I tried to add your code into mine, please see below
let
Source = Excel.CurrentWorkbook(){[Name="Input_Table"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Product", type text}, {"Market", type text}}),
#"Filtered Rows" = Table.SelectRows(#"Changed Type", each [Product] <> null and [Product] <> ""),
#"Removed Blank Rows" = Table.SelectRows(#"Filtered Rows", each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null}))),
#"Removed Errors" = Table.RemoveRowsWithErrors(#"Removed Blank Rows", {"Product"}),
#"Sorted Rows" = Table.Sort(#"Removed Errors",{{"Product", Order.Ascending}, {"Market", Order.Ascending}}),
#"Group" = Table.Group(#"Sorted Rows",{"Product"},{{"x",(x)=>{Table.FirstValue(x)} & x[Market]}}[[x]],
#"To_table" =Table.FromColumns(#"Group",List.Zip(group[x])))
in
#"To_table"
I receive an error saying "An error occurred in the ‘’ query. Expression.Error: The name 'To_table' wasn't recognized. Make sure it's spelled correctly". Am sure that, at this stage, it is a minor correction that stops me from the final result. would you be so kind to identify this error?
thank you in advance,
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 🙂
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.