An update: I was trying to do this with Power Query import into Power bi. I didn't realize how easy it would have been to do with DAX. Thanks for the answers that were specific to Power Query Like I originally specified.
I am trying to add a column to an import in power query to import to power bi that shows the next time an order happens, to let me see if a re-order was made / known at that point in time.
Sample data
Orders Table
| Item | OrderDate |
|---|---|
| Red | 1/1/2024 |
| Blue | 1/1/2024 |
| Yellow | 1/1/2024 |
| Red | 2/1/2024 |
| Yellow | 2/1/2024 |
| Red | 3/1/2024 |
| Yellow | 3/1/2024 |
The result table would show the next date an order happens, for each order, with the join being on the "Item" and the date being the first record greater than the current row date.
Result
| Result | ||
|---|---|---|
| Item | OrderDate | Next Order Date |
| Red | 1/1/2024 | 2/1/2024 |
| Blue | 1/1/2024 | null |
| Yellow | 1/1/2024 | 2/1/2024 |
| Red | 2/1/2024 | 3/1/2024 |
| Yellow | 2/1/2024 | 3/1/2024 |
| Red | 3/1/2024 | null |
| Yellow | 3/1/2024 | null |
I have accomplished this via merged queries using the transform button and then conditional columns to narrow down the result (creating a table with a full outer join), but given the size of the dataset this join creates far too many rows in the import that are then just removed from the dataset.
I would like to use a column formula for selectrows, which I think would involve table.selectrows (table.group), but also needs to join on the item and the next order date being greater than the order data in each row.
I see lots of information on selectrows, or group by, but not on an efficent way to pass this back to the mysql database.
if I were writing the mysql statement it would be
select
a.item
,a.orderdate
,min(nextorder.orderdate) as NextOrderDate
from orders a
left join orders as NextOrder on
a.item=NextOrder.item
and
NextOrder.orderdate>a.orderdate
group by
a.item
,a.orderdate`
DAX measure is usually an elegant way to address such questions,
Next =
SELECTCOLUMNS(
OFFSET( 1, ORDERBY( Orders[OrderDate] ), PARTITIONBY( Orders[Item] ) ),
Orders[OrderDate]
)
