Group based on condition

[Anonymous]

Hello Communities,

I am not sure how to group below into power bi power query?

I am trying to group multiple column based on the condition,

I want to group id column if rows numbers are same (Example:20657) and group Answer column by “Y” and “N” and group Area column if both rows are same (Example Americas).

Id	Answer	Area
20657	Y	Americas
20657	N	Americas
20933	N	Americas
20348	Y	Americas

Expecte Result

Id	Answer	Area	Result
20657	Y	Americas	2
20657	N	Americas	2
20933	N	Americas	1
20348	Y	Americas	1

[parry2k · Accepted solution]

@Anonymous hope attached will help, you can group by id and also put allrows in group and then expand it.

[Anonymous]

Thank you so much Parry.It is working perfectly fine.

[v-chuncz-msft]

@Anonymous,

You may try to add a custom column.

let
    r = _
in
    Table.RowCount(Table.SelectRows(Source, each [Id] = r[Id] and [Area] = r[Area]))

[Anonymous]

Hi Sam,

Please see below i am getting below error,From error i can say it is sheet and table error but i am not sure how dealt with this error?

let
    Source = Excel.Workbook(File.Contents("C:\Users\deletemondya.xlsx"), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Id", Int64.Type}, {"Answer", type text}, {"Area", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each let
    r=_
in 
  Table.RowCount(Table.SelectRows(Source,each[Id]=r[Id]
  and [Area]=r[Area]))),
    Custom = #"Added Custom"{0}[Custom]
in
    Custom