Forum Discussion

Kudol's avatar
Kudol
Regular Visitor
9 years ago
Solved

How to create new column based on duplicate values

Hello, i need help with creating new column. It is hard to explain so here is example:   Table 1. GUID; Email; 1111; [email protected]; 1111; [email protected] 1112; [email protected] 1113; 13@g...
Need Help
  • ImkeF's avatar
    ImkeF
    9 years ago

    This is a pivot-operation that you perform in the query-editor:

     

    let
    Source = Source,
    #"Grouped Rows" = Table.Group(Source, {"GUID"}, {{"Partition", each _, type table}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Partition], "Index", 1,1)),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Custom"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom", {"GUID", "Email", "Index"}, {"GUID", "Email", "Index"}),
    #"Added Custom1" = Table.AddColumn(#"Expanded Custom", "Custom", each "Email "),
    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Added Custom1", {{"Index", type text}}, "de-DE"),{"Custom", "Index"},Combiner.CombineTextByDelimiter(":", QuoteStyle.None),"Merged"),
    #"Pivoted Column" = Table.Pivot(#"Merged Columns", List.Distinct(#"Merged Columns"[Merged]), "Merged", "Email")
in
    #"Pivoted Column"

    Please let me know if you need help implementing this.

  • v-qiuyu-msft's avatar
    v-qiuyu-msft
    9 years ago

    Hi Kudol,

     

    In your  scneaior, I would suggest you place those email values within each GUID group in one column, you can refer to below sample:

     

    Rank = RANKX(FILTER(ALL(Table1),'Table1'[GUID]=EARLIER(Table1[GUID])),'Table1'[Email],,ASC)

     

    Rnk = IF('Table1'[Rank]<>1,'Table1'[Rank]-1)

     

    ParEmail = CALCULATE(FIRSTNONBLANK('Table1'[Email],1),FILTER(ALLEXCEPT(Table1,'Table1'[GUID]),'Table1'[Rank]=EARLIER(Table1[Rnk])))

     

    Emails = CALCULATE(PATH(Table1[Email],Table1[ParEmail]),CALCULATETABLE(FILTER('Table1',Table1[Rank]=MAX('Table1'[Rank])),ALLEXCEPT(Table1,'Table1'[GUID])))

     

     

    Best Regards,
    Qiuyun Yu

ImkeF's avatar
ImkeF
Community Champion
9 years ago

This is a pivot-operation that you perform in the query-editor:

 

let
    Source = Source,
    #"Grouped Rows" = Table.Group(Source, {"GUID"}, {{"Partition", each _, type table}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Partition], "Index", 1,1)),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Custom"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom", {"GUID", "Email", "Index"}, {"GUID", "Email", "Index"}),
    #"Added Custom1" = Table.AddColumn(#"Expanded Custom", "Custom", each "Email "),
    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Added Custom1", {{"Index", type text}}, "de-DE"),{"Custom", "Index"},Combiner.CombineTextByDelimiter(":", QuoteStyle.None),"Merged"),
    #"Pivoted Column" = Table.Pivot(#"Merged Columns", List.Distinct(#"Merged Columns"[Merged]), "Merged", "Email")
in
    #"Pivoted Column"

Please let me know if you need help implementing this.

diegox41's avatar
diegox41
New Member
3 years ago

Hello dear ImkeF,

I successfully used your code as a base for my specific case and got a result, however, I don't know if I'm being efficient enough and I get a lot of redundancies that I have to manually correct.

 

In my case, I'm scrapping e-commerce data for database analysis and my tables look like the following:

Table 1.

SKU; Category1;Category2

1111; flowers;gifts

1111; gifts;packs

1112; teddybear;gift

1113; roses;graduationgift


The first difference is that I have three columns instead of two as the example and your solution above, so I used this code:

let
    Source = Source,
    #"Removed Other Columns" = Table.SelectColumns(Source,{"Content"}),
    #"Added Custom" = Table.AddColumn(#"Removed Other Columns", "Custom", each Excel.Workbook([Content])),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Name", "Data", "Item", "Kind", "Hidden"}, {"Custom.Name", "Custom.Data", "Custom.Item", "Custom.Kind", "Custom.Hidden"}),
    #"Removed Other Columns1" = Table.SelectColumns(#"Expanded Custom",{"Custom.Name", "Custom.Data"}),
    #"Expanded Custom.Data" = Table.ExpandTableColumn(#"Removed Other Columns1", "Custom.Data", {"Column1", "Column2", "Column3", "SKU_product", "Categoria1", "Categoria2"}, {"Column1", "Column2", "Column3", "SKU_product", "Categoria1", "Categoria2"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded Custom.Data", each ([Custom.Name] <> "Sheet 1")),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Custom.Name", "Column1", "Column2", "Column3"})
in
    #"Removed Columns"

The problem is that I get the following results with duplicated values as in "gifts";"gifts", resulting in lots of unnecessary columns that I have to somehow remove later on.

SKU; Category1;Category2;Category3;Category4

1111; flowers;gifts;gifts;packs

1112; teddybear;gift

1113; roses;graduationgift

 

I'd greatly appreciate your support on this and thank you in advance.

Best