Find Products Running in Consecutive Weeks

[LiamFallows]

I want to write a query on a work order report to return all work orders for products that run in consecutive weeks.

A small sample of the table is below and the desired result of my query would return a column I could filter in order to hide products which do not run in consecutive weeks. It's important that I capture both orders, the first order and the second order running in the following week.

I've been able to achieve a similar result in excel using nested countif statements but I'd prefer to use power query. Group by has not helped me, as I only return results that run in the same week using that method.

Could anyone help me please?

Start Date	Order Number	Product	Product Description	Desired Result
19/03/2024	3003168	589883	Product 1	Product Running in Consecutive Weeks
27/03/2024	3003169	589883	Product 1	Product Running in Consecutive Weeks
15/03/2024	3003982	590413	Product 2	Product Running in Consecutive Weeks
21/03/2024	3003992	590413	Product 2	Product Running in Consecutive Weeks
14/03/2024	3003971	590415	Product 3	OK
28/03/2024	3003971	590415	Product 3	OK

[dufoq3 · Accepted solution]

Hi @LiamFallows, try this.

I'm not sure about last two rows with OK (bacause the order number is the same). My logic is: If orders are in consecutive weeks (not in the same week but in consecutive) then result is Product Running in Consecutive Weeks else OK.

Result:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jcw7CoAwEADRq8jWgewnMbu3sA+ptBdE768iQogodlM8Jmcg8yiekQM4EEShXo+KaqpyxLDM0zauHUFxGTg9tL1rio025VMbBqo1X29qtX1oCq1OdOtYabne+luXHQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Start Date" = _t, #"Order Number" = _t, Product = _t, #"Product Description" = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"Start Date", type date}}, "sk-SK"),
    AD_YearWeek = Table.AddColumn(ChangedType, "Week of Year", each Date.Year([Start Date]) * 100 + Date.WeekOfYear([Start Date]), Int64.Type),
    GroupedRows = Table.Group(AD_YearWeek, {"Product"}, {
     { "All", each Table.AddColumn(
                       Table.AddIndexColumn(Table.Sort(_, {{"Start Date", Order.Ascending}}), "Index", 0, 1, Int16.Type),
                       "Check", (x)=> if (try x[Week of Year] +1 = [Week of Year]{x[Index]+1} otherwise false) or (try x[Week of Year] -1 = [Week of Year]{x[Index]-1} otherwise false) then "Product Running in Consecutive Weeks" else "OK", type text), type table }
  }),
    CombiedAll = Table.Combine(GroupedRows[All]),
    RemovedColumns = Table.RemoveColumns(CombiedAll,{"Week of Year", "Index"})
in
    RemovedColumns

[lbendlin]

how big is your actual data volume?  Do you want this in DAX or in Power Query?

[LiamFallows]

Depending on the time of year the data set is typically around 250-300 rows long by around 5 columns wide. It comes from a piece of report writing software that's pointed at an erp system and it's in xlsx format.

That file gets pushed into a network location that I've picked up on a query. The only reason I've chosen power query is just lack of experience with dax so that's why it would be my preference