Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Group based on condition

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

 

Need Help

4 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

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

  • v-chuncz-msft's avatar
    v-chuncz-msft
    Community Support
    7 years ago

    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's avatar
      Anonymous
      Not applicable
      7 years ago

      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