How to create new column based on duplicate values

[Kudol]

Hello, i need help with creating new column. It is hard to explain so here is example:

Table 1.

GUID; Email;

1111; 1111@email.com;

1111; example@email.com

1112; example1@email.com

1113; 13@gmail.com

and I need new table, which will look like this

Guid; Email; Email2; Email-3... etc

1111; 1111@email.com; example@email.com

1112; example1@email.com

1113; 13@gmail.com

thank you for affording me your time

[ImkeF · Accepted solution]

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.

[ImkeF · Accepted solution]

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]

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

[Pranshu]

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

ACBD5530	12:21:48 AM
6CE511AA	12:21:53 AM
CCD116AA	12:21:58 AM
CCD116AA	12:22:02 AM
6CE511AA	12:22:04 AM
ACBD5530	12:22:05 AM
ACBD5530	12:22:48 AM
ACBD5530	12:22:50 AM
6CE511AA	12:22:53 AM
ACBD5530	12:23:01 AM
6CE511AA	12:23:05 AM
ACBD5530	12:23:10 AM

and using your code, my output is

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

6CE511AA	12:21:53 AM	12:22:04 AM	12:22:53 AM	12:23:05 AM	null	null
ACBD5530	12:21:48 AM	12:22:05 AM	12:22:48 AM	12:22:50 AM	12:23:01 AM	12:23:10 AM
CCD116AA	12:21:58 AM	12:22:02 AM	null	null	null	null

but i need table headers should be as follows

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

6CE511AA	12:21:53 AM	12:22:04 AM	12:22:53 AM	12:23:05 AM	null	null
ACBD5530	12:21:48 AM	12:22:05 AM	12:22:48 AM	12:22:50 AM	12:23:01 AM	12:23:10 AM
CCD116AA	12:21:58 AM	12:22:02 AM	null	null	null	null

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?

ACBD5530	OU	0:21:48
6CE511AA	OU	0:21:53
CCD116AA	OU	0:21:58
CCD116AA	IN	0:22:02
6CE511AA	IN	0:22:04
ACBD5530	IN	0:22:05
ACBD5530	OU	0:22:48
ACBD5530	IN	0:22:50
6CE511AA	OU	0:22:53
ACBD5530	OU	0:23:01
6CE511AA	IN	0:23:05
ACBD5530	IN	0:23:10

[ImkeF]

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"

[Kudol]

Thank you, it works.

[Kudol]

Hello, i need help with creating new column. It is hard to explain so here is example:

Table 1.

GUID; Email;

1111; 1111@email.com;

1111; example@email.com

1112; example1@email.com

1113; 13@gmail.com

and I need new table, which will looks like this

Guid; Email; Email2; Email-3...

1111; 1111@email.com; example@email.com

1112; example1@email.com

1113; 13@gmail.com

thank you for affording me your time

1113; 13@gmail.com

[v-qiuyu-msft · Accepted solution]

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