How to count different items in the same column

[Alta88]

I've got a report that has 3 different appointment types listed under the same column heading. 2 types are lumped together and 1 is it's own category. I need to get the total of each appointment type for the coming day. How do I go about this? 

[ronrsnfld]

How to do this depends on what you want for output.

For example, if you want a table where the dates are in one column, and the Appointment Types are listed in individual columns, then the following code will do that.

Assuming your data is in an Excel table:

• Select some cell in your original table
• Data => Get&Transform => From Table/Range or `From within sheet`
•  When the PQ UI opens, navigate to `Home => Advanced Editor`
•  Make note of the Table Name in Line 2 of the code.
•  Replace the existing code with the **M-Code** below
•  Change the table name in line 2 of the pasted code to your "real" table name
• Examine any comments, and also the `Applied Steps` window, to better understand the algorithm and steps

let

//change Table name in next line to your "real" table name in your workbook
    Source = Excel.CurrentWorkbook(){[Name="Table11"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Appointment Date", type date}, {"Appointment Type", type text}}),

//Group by Date
// Then Pivot by Appointment Type after equating the LVL's
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Appointment Date"}, {
        {"pivot", (t)=>let 

        //Transform the "Type" column to "equate" the LVL types
            combineTypes = Table.TransformColumns(t, {"Appointment Type", each if Text.StartsWith(_,"LVL") then "LVL" else _}),

        //pivot on Appointment Type with a Count aggregation
            pivot = Table.Pivot(combineTypes,List.Distinct(combineTypes[Appointment Type]), "Appointment Type", "ID", List.Count)
        in 
            pivot}
        }),

//remove now unneeded date column and expand the Pivoted table
    #"Removed Columns" = Table.RemoveColumns(#"Grouped Rows",{"Appointment Date"}),

//create dynamic list of column names
//I am assuming that the first column is "Appointment Date".  If not, a more complex algorithm will be needed.
colNames = List.Distinct(List.Accumulate(#"Removed Columns"[pivot],{},(state,current)=> state & Table.ColumnNames(current))),

    #"Expanded pivot" = Table.ExpandTableColumn(#"Removed Columns", "pivot", colNames,colNames),

//set the data types
    typeIt = Table.TransformColumnTypes(#"Expanded pivot", 
        {{colNames{0}, type date}} & List.Transform(List.RemoveFirstN(colNames,1), each {_, Int64.Type}))
in
    typeIt

[Alta88]

Cool. Thanks so much!

[Alta88]

Thanks, I tried implementing this but got confused by the syntax used. Is there a simpler way to do this?

[jsaunders_zero9 · Accepted solution]

@Alta88 

See below steps to produce using the UI.

1. Add Custom Column

2. Create if statement to determine which "Group" the items should fall into.

if [Appointment Type] = "Service" then "Service" else "LVLn"

3. Add Group By, right click the column you just created call TypeGroup, Select Group By

4. Group By Appointment Date & TypeGroup

5. End Result

[Alta88]

for example: 

Levels 1 and 2 are the ones lumped together. 

[jsaunders_zero9]

Could you add a helper column to group on?

= Table.AddColumn(#"Changed Type", "TypeGroup", each if [Appointment Type] = "Service" then "Service" else "LVLn")

Then group the new column on Appointment Date and the TypeGroup column

= Table.Group(#"Added Custom", {"Appointment Date", "TypeGroup"}, {{"Count", each Table.RowCount(_), type number}})