Forum Discussion
Find Products Running in Consecutive Weeks
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 |
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
3 Replies
how big is your actual data volume? Do you want this in DAX or in Power Query?
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
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
