March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
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?
Solved! Go to Solution.
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
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:
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
Cool. Thanks so much!
Thanks, I tried implementing this but got confused by the syntax used. Is there a simpler way to do this?
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
for example:
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")
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}})
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
User | Count |
---|---|
24 | |
13 | |
11 | |
10 | |
7 |
User | Count |
---|---|
43 | |
26 | |
18 | |
16 | |
11 |