Forum Discussion
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; [email protected]
and I need new table, which will look like this
Guid; Email; Email2; Email-3... etc
1111; [email protected]; [email protected]
1112; [email protected]
1113; [email protected]
thank you for affording me your time
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.
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
7 Replies
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.
Thank you, it works.
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 TableGUID; 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 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"
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
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; [email protected]
and I need new table, which will looks like this
Guid; Email; Email2; Email-3...
1111; [email protected]; [email protected]
1112; [email protected]
1113; [email protected]
thank you for affording me your time
1113; [email protected]
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
