Forum Discussion
Create a count rows & group by column in Power Query
Hi all,
I created this column in DAX to retrieve "EMEA" if a user has more than one market assigned. It´s runnging ok. The thing is that I would like to create this same column in Power Query (I guess using M language) so that I can handle it there, as columns created with dax can´t be loaded into Power Query...
Thanks in advance
Hi Anonymous ,
You can copy and paste the following codes in your Advanced Editor to achieve it using Power Query, please find the details in the attachment.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8kjNSc1T0lEKyC8qKU1PzAEyDQ30jYz0jQyMDJVidZBV5CTmpeCWdytKzEtOxaefkA3BBYmZecRJWxJwHxb5UO+YUgMDIzNPfEagOBGLChRPIsk7FpVklBbhCQXsCmAmxAIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [User_Principal_Name = _t, Market_Name = _t, Date = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"User_Principal_Name", type text}, {"Market_Name", type text}, {"Date", type date}}), #"Grouped Rows" = Table.Group(#"Changed Type", { "User_Principal_Name"}, {{"Count", each List.Count(List.Distinct([Market_Name])), Int64.Type}, {"Detail", each _ , type table [User_Principal_Name=nullable text, Market_Name=nullable text, Date=nullable date]}}), #"Expanded Detail" = Table.ExpandTableColumn(#"Grouped Rows", "Detail", {"Market_Name", "Date"}, {"Market_Name", "Date"}), #"Added Custom" = Table.AddColumn(#"Expanded Detail", "New Market", each if [Count]=1 then [Market_Name] else "EMEA"), #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Count"}) in #"Removed Columns"Best Regards
2 Replies
Anonymous , You need to create a copy of the table and use group by with count of the column and then merge it back with main table
refer if needed
https://powerbloggerbi.com/2016/06/29/power-query-total-and-subtotal/
Hi Anonymous ,
You can copy and paste the following codes in your Advanced Editor to achieve it using Power Query, please find the details in the attachment.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8kjNSc1T0lEKyC8qKU1PzAEyDQ30jYz0jQyMDJVidZBV5CTmpeCWdytKzEtOxaefkA3BBYmZecRJWxJwHxb5UO+YUgMDIzNPfEagOBGLChRPIsk7FpVklBbhCQXsCmAmxAIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [User_Principal_Name = _t, Market_Name = _t, Date = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"User_Principal_Name", type text}, {"Market_Name", type text}, {"Date", type date}}), #"Grouped Rows" = Table.Group(#"Changed Type", { "User_Principal_Name"}, {{"Count", each List.Count(List.Distinct([Market_Name])), Int64.Type}, {"Detail", each _ , type table [User_Principal_Name=nullable text, Market_Name=nullable text, Date=nullable date]}}), #"Expanded Detail" = Table.ExpandTableColumn(#"Grouped Rows", "Detail", {"Market_Name", "Date"}, {"Market_Name", "Date"}), #"Added Custom" = Table.AddColumn(#"Expanded Detail", "New Market", each if [Count]=1 then [Market_Name] else "EMEA"), #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Count"}) in #"Removed Columns"Best Regards
