I would break this into 3 queries, then append all three.
First, I would make three new blank queries, named First, Breaks, and Meals. Suppose your main table is named FactTable.
For the First query, in the formula bar, you want to group the FactTable. I can't quite be 100% sure on the syntax because I'm not at my desk, but its:
=Table.Group(FactTable,//and group by Name and Date. Just use the GUI button, and be sure to select an aggregation -- All Rows. Name that column "Details".
Next, add a column to get each first row:
= Table.AddColumn(Source, "FirstRows", each [Details]{0})
You can now delete your original columns and then expand the Details column.
For the Breaks query, in the formula bar, type:
= Table.SelectRows(FactTable, each [Code] = "Break")
Then group this table by Name and Date, also using the All Rows aggregation, also named "Details". But for the multiple breaks per
day, we need to add an index column to each nested table:
= Table.TransformColumns(Source, {{"Details", each Table.AddIndexColumn([Details], "Index" 1,1, type number)}})
Now you can get rid of your original columns and then expand your Drtails column. Now you can transform the Code column:
= Table.TransformColumns(#"Removed Columns", {{"Code", each Text.Insert([Code], 0, if [Index] = 1 then "First " else if [Index] = 2 then "Second " else if [Index] = 3 then "Third " else "Fourth ")}})
Now you can remove the Index Column.
The Lunch query is straightforward:
= Table.SelectRows(FactTable, each [Code] = "Meal")
Now that you have all three tables, double check to make sure that the column names and types are the same, and then you can make your final query. Add a blank query, and in the Advanced Editor, type:
let
EmptyTable = Table.FromRows({{null, null, null}}, type table [Name = text, Code = text, Date = date]),
FirstTable = if Table.IsEmpty(First) then EmptyTable else First,
SecondTable = if Table.IsEmpty(Breaks) then EmptyTable else Breaks,
ThirdTable = if Table.IsEmpty(Meals) then EmptyTable else Meals,
FinalTable = Table.Combine({FirstTable, SecondTable, ThirdTable})
in FinalTable
That should do it!
--Nate
I’m usually answering from my phone, which means the results are visualized only in my mind. You’ll need to use my answer to know that it works—but it will work!!
