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.

  • Kudol's avatar
    Kudol
    Regular Visitor
    9 years ago

    Thank you, it works.

  • Pranshu's avatar
    Pranshu
    New Member
    8 years ago

    Hey. Thank you very much for the solution. i tested it and it works totally fine. but i need a little different output....
    This is my input Table

    GUID;  Time

    ACBD553012:21:48 AM
    6CE511AA12:21:53 AM
    CCD116AA12:21:58 AM
    CCD116AA12:22:02 AM
    6CE511AA12:22:04 AM
    ACBD553012:22:05 AM
    ACBD553012:22:48 AM
    ACBD553012:22:50 AM
    6CE511AA12:22:53 AM
    ACBD553012:23:01 AM
    6CE511AA12:23:05 AM
    ACBD553012:23:10 AM

     

    and using your code, my output is

    GUID;  Time :1  Time :2Time :3Time :4Time :5Time :6

    6CE511AA12:21:53 AM12:22:04 AM12:22:53 AM12:23:05 AMnullnull
    ACBD553012:21:48 AM12:22:05 AM12:22:48 AM12:22:50 AM12:23:01 AM12:23:10 AM
    CCD116AA12:21:58 AM12:22:02 AMnullnullnullnull

     

    but i need table headers should be as follows

    GUIDout :1in :1out :2in :2out :3in :3

    6CE511AA12:21:53 AM12:22:04 AM12:22:53 AM12:23:05 AMnullnull
    ACBD553012:21:48 AM12:22:05 AM12:22:48 AM12:22:50 AM12:23:01 AM12:23:10 AM
    CCD116AA12:21:58 AM12:22:02 AMnullnullnullnull

     

    how can i achieve that? can u please provide a code for it. i am new to powerbi. help would be really appreciated

     

    i can also create a table like this. what is more suitable?

    ACBD5530OU0:21:48
    6CE511AAOU0:21:53
    CCD116AAOU0:21:58
    CCD116AAIN0:22:02
    6CE511AAIN0:22:04
    ACBD5530IN0:22:05
    ACBD5530OU0:22:48
    ACBD5530IN0:22:50
    6CE511AAOU0:22:53
    ACBD5530OU0:23:01
    6CE511AAIN0:23:05
    ACBD5530IN0:23:10

     

    • ImkeF's avatar
      ImkeF
      Community Champion
      8 years ago

      So you just need different column names?

      Check out this solution please:

       

      let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dc4xDoAgDIXhq5jODi2lhHRDcPQEhPtfQxKDGEznL+37a4WUjyLCCDuQU0fq45YuaHuFkE8hSukl4UE5F6LwpWiQU3TGw05+0JLRSUyahT8SNLdm/HLFimRcsZ3BSs9WuwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [GUID = _t, Time = _t]),
    #"Grouped Rows" = Table.Group(Source, {"GUID"}, {{"Partition", each _, type table}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Partition], "Index", 0,1)),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Custom"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom", {"GUID", "Time", "Index"}, {"GUID", "Time", "Index"}),
    IntegerDividedColumn = Table.TransformColumns(#"Expanded Custom", {{"Index", each Number.IntegerDivide(_, 2) + 1, Int64.Type}}),
    NumberOfColumnPairs = Table.RowCount(IntegerDividedColumn) / 2,
    ListOfPrefixes = List.Repeat({"out :", "in :"}, NumberOfColumnPairs),
    Custom1 = Table.FromColumns( Table.ToColumns(IntegerDividedColumn) & {ListOfPrefixes} ),
    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(Custom1, {{"Column3", type text}}, "en-GB"),{"Column4", "Column3"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"ColNames"),
    #"Pivoted Column1" = Table.Pivot(#"Merged Columns", List.Distinct(#"Merged Columns"[ColNames]), "ColNames", "Column2"),
    #"Renamed Columns" = Table.RenameColumns(#"Pivoted Column1",{{"Column1", "GUID"}})
in
    #"Renamed Columns"

       

  • 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