Forum Discussion

RobRayborn's avatar
RobRayborn
Helper IV
1 year ago

Rows left out from data source

I have incoming Excel workbooks that I receive monthly. Each workbook gives me one sheet that has columns for, [SKU, SHIP DATE (M/D/Y), and Qantity]. 
From this data I get how many SKUs are to ship in the provided upcoming months, usually extending out 9 months.

My problem is that I pull these workbooks in using <Get Data, From Folder> and if the prevous workbook had say 10 for 10/1/2024, 15 for 11/1/2024, 25 for 12/1/2024, 10 for 1/1/2025, then in the next received workbook the line for date 12/1/2024 is ommitted I cannot use my DAX measure that provides me with the LASTNONBLANK becuase it will still be looking at the older workbook amount of 25 for 12/1/2024.  
There isn't a row that has 12/1/2024, Qty -. The row for that date is just left off the list.

If I input a zero by adding a +0 to the LASTNONBLANK measure this doesn't work. 

In Power Query how can I add the missing row of data back into the list with a Zero QTY by using the start of the [SHIP DATE] and the last [SHIP DATE] provided (not all SKUs are missing a row of data, so I'm guarentied a Min and Max range)?

Need Help
Tutorial Requests

4 Replies

  • dufoq3's avatar
    dufoq3
    Community Champion
    1 year ago

    Upload at least 2 of your files with few rows of dummy data i.e. to google drive or dropbox etc and provide at least a screenshot of expected result based on sample data please.

    • RobRayborn's avatar
      RobRayborn
      Helper IV
      1 year ago

      Three Entry Dates, keep an eye on the Date Due December range. It disapears in the Date created 9/26/2024 range.  Without a December 2024 entry of 0, my DAX measure of LASTNONBLANK continues to pick up the December amount from the 8/29/2024 Date created range.

      Date createdPARTNBRDATE DUEQTY DUE
      7/29/2024ABC8/6/2024960
      7/29/2024ABC8/9/202414
      7/29/2024ABC8/23/20246
      7/29/2024ABC9/3/2024480
      7/29/2024ABC9/9/202413
      7/29/2024ABC9/17/2024480
      7/29/2024ABC9/20/202414
      7/29/2024ABC10/1/202417
      7/29/2024ABC10/1/2024480
      7/29/2024ABC11/1/20247
      7/29/2024ABC11/5/2024480
      7/29/2024ABC11/15/202442
      7/29/2024ABC11/26/2024480
      7/29/2024ABC12/6/202416
      7/29/2024ABC12/10/2024640
      7/29/2024ABC12/19/202410
      8/29/2024ABC9/10/20242
      8/29/2024ABC9/20/202411
      8/29/2024ABC9/27/202416
      8/29/2024ABC10/15/2024480
      8/29/2024ABC10/22/2024480
      8/29/2024ABC10/29/202419
      8/29/2024ABC11/5/2024480
      8/29/2024ABC11/12/20246
      8/29/2024ABC11/26/20245
      8/29/2024ABC11/26/2024480
      8/29/2024ABC12/10/202421
      8/29/2024ABC12/19/202421
      9/26/2024ABC8/27/20241
      9/26/2024ABC10/15/2024480
      9/26/2024ABC10/22/2024480
      9/26/2024ABC11/5/2024480
      9/26/2024ABC11/26/2024480
      9/26/2024ABC1/5/2025360
      9/26/2024ABC2/6/2025190
  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi RobRayborn ,

     

    As a workaround, you can create a date table to fill in the missing dates with a quantity of 0. Here’s a step-by-step guide to achieve this:

    1.Create a list of all dates within the range: Refer to  Create Calendar Table Using Power Query M Language (mssqltips.com)
    2.Merge the list of dates with your data: Merge this list with your existing data to ensure every date within the range is represented.
    3.Fill missing dates with zero quantity: Replace any missing quantities with 0.

     

    Best Regards,

    Stephen Tao

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.