removing duplicated rows without losing information

[GeorgeGiannakis]

Hi all,

Hope you are well 🙂

I need some help with the below table1.

The table contains two columns, "Product" and "Market".

"Product"column contains duplicated entries.

I need to create a new table (Table2) that will capture each product only once and then have multiple columns against each row, each containing the individual market(s) linked to this product.

I tried various power query options such as transpose, pivot and unpivot, but it took me nowhere 😞

Table1

Product	Market
SKU1	UK
SKU1	Ireland
SKU2	France
SKU2	Spain
SKU2	Portugal

Table2

Product	Market1	Market2	Market3
SKU1	UK	Ireland
SKU2	France	Spain	Portugal

Thank you in advance 🙂

[George_Gian]

hi @AlienSx , forgot to ask, how can i flag this as resolved ? thank you

[George_Gian]

hi @AlienSx , it worked pefrectly, thank you for that. so, it was not a matter of transposing/pivoting, it was a matter of grouping/consolidating instead, got it 🙂

[George_Gian]

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,

[AlienSx · Accepted solution]

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

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 🙂

[AlienSx]

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

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

[AlienSx]

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. 

[GeorgeGiannakis]

Thank you @AlienSx , just finished the "M is for Data Monkey" by K.Puls and M.Escobar and was looking for new material

[AlienSx]

Hello, @GeorgeGiannakis I have not renamed columns to Market1, Market2 etc. But it's doable.

let
    Source = your_table,
    group = Table.Group(Source, {"Product"}, {{"x", (x) => {Table.FirstValue(x)} & x[Market]}})[[x]],
    to_table = Table.FromColumns(List.Zip(group[x]))
in
    to_table