Forum Discussion

GeorgeGiannakis's avatar
GeorgeGiannakis
New Member
2 years ago
Solved

removing duplicated rows without losing information

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

ProductMarket
SKU1UK
SKU1Ireland
SKU2France
SKU2Spain
SKU2Portugal

 

Table2

ProductMarket1Market2Market3
SKU1UKIreland 
SKU2FranceSpainPortugal

 

Thank you in advance 🙂

Need Help
  • AlienSx's avatar
    AlienSx
    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]))

10 Replies

  • AlienSx's avatar
    AlienSx
    Super User
    2 years ago

    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
  • George_Gian's avatar
    George_Gian
    Regular Visitor
    2 years ago

    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's avatar
      AlienSx
      Super User
      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's avatar
        George_Gian
        Regular Visitor
        2 years ago

        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 🙂

  • George_Gian's avatar
    George_Gian
    Regular Visitor
    2 years ago

    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 🙂