Create a count rows & group by column in Power Query

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