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 (Exam...
Need Help
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