Another join problem based on time range RRS feed

  • Question

  • Hi there - I hope someone can help me out:

    This is a dataset of a restaurant, one table has all the sales details and the time of order for every single item and the other has some demographic info of the guests, their arrival and leave time, total spending, tips, etc. (similar to invoice "header" and "detail" structure)

    I need to link the two tables (to do analysis on item level) and the only thing I could think of is to make the link based on table number (which is available in both datatables) and all datetime entries that fall between arrival time and leave time.

    The relevant fileds are:

    "Turnover table": Arrival time (datetime), Leave time (datetime), Table no., Total revenue, Tips received, VAT codes, etc...
    "Details table": Order time (datetime), Table no., Item name, Item type, Item price, etc....

    How should the M code look like? I was trying to use the hint from another date range based example - but I got confused.
    Thanks for the help!



    Thursday, December 6, 2018 3:49 PM


  • Hi Laszlo,

    In general, you would add a new column to the Turnover table that, for each row, gets a filtered table from the Details table based on the specified criteria, for example:

    AddedColumn = Table.AddColumn( PreviousStepName, (i) => Table.SelectRows( Details, (j) =>
    i[Table No] = j[Table No] and
    i[Arrival Time] < j[Order Time] and
    i[Leave Time] > j[Order Time]

    Thursday, December 6, 2018 9:28 PM

All replies