Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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.
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
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
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.
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
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
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 |
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"
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Thank you, it works.
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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
99 | |
82 | |
70 | |
60 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |