• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to create a column with specific condition in Power Query or Power BI?

N

norpriest

New Member
Hello,

I have attached my sample workbook for better understanding.
I would like to know how to create column "Code" in Power Query or Power BI bases on conditions in sample workbook.

The COUNTIFS function was perfect to use in Excel for this scenario, but in Power Query or Power BI, I didn't know how to do or what kind of function I should use in this scenario.

Could you please let me know the solutions to this.
Thank you.

Nor
 

Attachments

  • COUNTIFS.xlsx
    12.1 KB · Views: 4
Here is one method

Code: 
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"sales rep", type text}, {"asst manager", type text}, {"manager", type text}, {"cleaner", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each [sales rep] &[asst manager] &[manager] &[cleaner])
in
    #"Added Custom"

and here is a second option

Code: 
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"sales rep", type text}, {"asst manager", type text}, {"manager", type text}, {"cleaner", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Text.Combine({[sales rep],[asst manager],[manager],[cleaner]}))
in
    #"Added Custom"
 
AlanSidman said:
Here is one method

Code: 
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"sales rep", type text}, {"asst manager", type text}, {"manager", type text}, {"cleaner", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each [sales rep] &[asst manager] &[manager] &[cleaner])
in
    #"Added Custom"

and here is a second option

Code: 
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"sales rep", type text}, {"asst manager", type text}, {"manager", type text}, {"cleaner", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Text.Combine({[sales rep],[asst manager],[manager],[cleaner]}))
in
    #"Added Custom"
Click to expand...

Thank you AlanSidman for the provided solutions.

Though I'm sorry I was asking a wrong question.

I was meant to ask about how to create columns: "sales rep", "asst manager", "manager", "cleaner".
Not the "Code" column since it's just a concatenation column.

I tried to edit my original post though but still could't see edit button.
I'll fix it soon.
 
I am not understanding what you are asking. Please show us in a file what your expected results should look like.
 
I believe it's something like

Code: 
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Roles = List.Buffer(List.Distinct(Source[role])),
    GroupBranch = Table.Group(Source, {"branch"}, {{"All", each _, type table [branch=text, role=text]}}),
    GroupPivAll = Table.AddColumn(GroupBranch, "GroupCount", each let
Grp = Table.Group([All], {"role"}, {{"Count", each Table.RowCount(_), Int64.Type}}),
Piv = Table.Pivot(Grp, Roles, "role", "Count", List.Sum)
in
   Piv),
    ExpandGroupPivAll = Table.ExpandTableColumn(GroupPivAll, "GroupCount", Roles, Roles),
    ExpandAll = Table.ExpandTableColumn(ExpandGroupPivAll, "All", {"role"}, {"role"}),
    Replace_null = Table.ReplaceValue(ExpandAll,null,0,Replacer.ReplaceValue,Roles),
    AddColCode = Table.AddColumn(Replace_null, "Code", each List.Accumulate(
Record.FieldValues( Record.SelectFields(_,Roles)), "", (state, current) => state & Text.From(current)), type text)
in
    AddColCode
 

Attachments

  • COUNTIFS.xlsx
    22.3 KB · Views: 11
GraH - Guido said:
I believe it's something like

Code: 
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Roles = List.Buffer(List.Distinct(Source[role])),
    GroupBranch = Table.Group(Source, {"branch"}, {{"All", each _, type table [branch=text, role=text]}}),
    GroupPivAll = Table.AddColumn(GroupBranch, "GroupCount", each let
Grp = Table.Group([All], {"role"}, {{"Count", each Table.RowCount(_), Int64.Type}}),
Piv = Table.Pivot(Grp, Roles, "role", "Count", List.Sum)
in
   Piv),
    ExpandGroupPivAll = Table.ExpandTableColumn(GroupPivAll, "GroupCount", Roles, Roles),
    ExpandAll = Table.ExpandTableColumn(ExpandGroupPivAll, "All", {"role"}, {"role"}),
    Replace_null = Table.ReplaceValue(ExpandAll,null,0,Replacer.ReplaceValue,Roles),
    AddColCode = Table.AddColumn(Replace_null, "Code", each List.Accumulate(
Record.FieldValues( Record.SelectFields(_,Roles)), "", (state, current) => state & Text.From(current)), type text)
in
    AddColCode
Click to expand...

Hi Grah - Guido,

This is what I was looking for.
Though I believe this is still too advanced for me since I still couldn't wrap my head around the thinking process behind it.

Anyway thank you for the answer and appreciated.
 
Thanks for the feedback. To help you on the way to understand my query, here is it again with some notes.
Paste it in the editor and the comments appear in green.

Code: 
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Roles = List.Buffer(List.Distinct(Source[role])), //extracts from previous step (which is a table) the column "role" and gets the unique values. Buffer it to keep the list in memory.  This makes it dynamic.
    GroupBranch = Table.Group(Source, {"branch"}, {{"All", each _, type table [branch=text, role=text]}}),  //Aggregate using all row to create a segmented table
    GroupPivAll = Table.AddColumn(GroupBranch, "GroupCount", each let
Grp = Table.Group([All], {"role"}, {{"Count", each Table.RowCount(_), Int64.Type}}),
Piv = Table.Pivot(Grp, Roles, "role", "Count", List.Sum)
in
   Piv), // is a custom column holding a "mini query"  It groups the sub table or segment, counts the rows and pivots the roles (the buffered list)  When you drill down on a table object, you can continue using the UI.  Then copy these lines from the advanced editor and paste it in an custom column.  Do not fortget to delete everything from the drill down on.
    ExpandGroupPivAll = Table.ExpandTableColumn(GroupPivAll, "GroupCount", Roles, Roles),  // Expand this tabble and use the columns that match a "role", again using the buffered list
    ExpandAll = Table.ExpandTableColumn(ExpandGroupPivAll, "All", {"role"}, {"role"}),  // Expand the segmented table, only need to recover the original rows by expanding the original role column
    Replace_null = Table.ReplaceValue(ExpandAll,null,0,Replacer.ReplaceValue,Roles),// Replace null values with zero, so we can create the code.  This is needed for all the columns with a count in it.  You guesses it, the buffered list once more.
    AddColCode = Table.AddColumn(Replace_null, "Code", each List.Accumulate(
Record.FieldValues( Record.SelectFields(_,Roles)), "", (state, current) => state & Text.From(current)), type text) // "_" creates a record for each row of the table.  But we only need the values from the "roles" columns.  Se we Select the fields (Record.SelectFields) by using the buffered list a last time.  Yet we need the values (record.FieldValues).  This gives us a list.  To concatenate each element we can use List.Accumulate.  The seed values is "", an empty string. state is the previous result and we concatenate the current value.  Since these values are numbers, we need to transform them to text first.  So for the first record it actually creates these intermediate values: "", "2", "21", "211", "2100".  The last value is finally retained.
in
    AddColCode
 
You must log in or register to reply here.
Back
Top