Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Alta88
Helper IV
Helper IV

How to count different items in the same column

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? 

1 ACCEPTED SOLUTION

@Alta88 

See below steps to produce using the UI.

 

1. Add Custom Column

jsaunders_zero9_0-1651284695841.png

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

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

jsaunders_zero9_1-1651284706694.png

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

jsaunders_zero9_2-1651284840862.png

4. Group By Appointment Date & TypeGroup

jsaunders_zero9_3-1651284879187.png

5. End Result

jsaunders_zero9_4-1651284912724.png

View solution in original post

6 REPLIES 6
ronrsnfld
Super User
Super User

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

ronrsnfld_0-1651402110099.png

 

 

Cool. Thanks so much!

 

Alta88
Helper IV
Helper IV

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

@Alta88 

See below steps to produce using the UI.

 

1. Add Custom Column

jsaunders_zero9_0-1651284695841.png

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

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

jsaunders_zero9_1-1651284706694.png

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

jsaunders_zero9_2-1651284840862.png

4. Group By Appointment Date & TypeGroup

jsaunders_zero9_3-1651284879187.png

5. End Result

jsaunders_zero9_4-1651284912724.png

Alta88
Helper IV
Helper IV

for example: 

 

Alta88_0-1651092692945.png

 

Levels 1 and 2 are the ones lumped together. 

Could you add a helper column to group on?

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

jsaunders_zero9_0-1651108068988.png

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}})

jsaunders_zero9_1-1651108134783.png

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors