Forum Discussion

LiamFallows's avatar
LiamFallows
New Member
2 years ago
Solved

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 DateOrder NumberProductProduct DescriptionDesired Result
19/03/20243003168589883Product 1Product Running in Consecutive Weeks
27/03/20243003169589883Product 1Product Running in Consecutive Weeks
15/03/20243003982590413Product 2Product Running in Consecutive Weeks
21/03/20243003992590413Product 2Product Running in Consecutive Weeks
14/03/20243003971590415Product 3OK
28/03/20243003971590415Product 3OK
Need Help
  • dufoq3's avatar
    dufoq3
    2 years ago

    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

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    2 years ago

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

    • LiamFallows's avatar
      LiamFallows
      New Member
      2 years ago

      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

  • dufoq3's avatar
    dufoq3
    Icon for Community Champion rankCommunity Champion
    2 years ago

    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