Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
GeorgeGiannakis
New Member

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 🙂

1 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]))

View solution in original post

10 REPLIES 10
George_Gian
Regular Visitor

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

George_Gian
Regular Visitor

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
Regular Visitor

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.

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

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. 

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

AlienSx
Super User
Super User

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors
Top Kudoed Authors