Forum Discussion

TD's avatar
TD
Copper Contributor
Nov 08, 2024

Extend Value to all Weeks going forward

Hi, My first post to community. I need help to extend value for all week endings from now. My Week ends on Saturday. First table is my current output from Power query where I have closing stock for each item as of week ending. As you can see there is no value for all week ends going forward. I want to get expected output table below as if there is no value for future weeks from now(), The value should be the previous Closing stock value. I can add the suggested solution as a last step in power query or as formula in excel.  I have 960 different Items with this type of data.

 

 

excel
Formulas and Functions
  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Platinum Contributor
    Nov 09, 2024

    I prefer to keep things simple and although you have 960 items the solution demonstrated in the attached example should work for you. It askes for some preparation in Excel (create a table with all item numbers and a table with all the week-ending-dates).

    See if you can apply the same steps to your actual data.

  • Lorenzo's avatar
    Lorenzo
    Silver Contributor
    Nov 09, 2024

    Hi TD 

    A Power Query option is attached

    // ExpandWeeklyValue - can be shortened
(table as table) as table =>
let
    Source = table,
    MinDate = Table.Min( Source, {"Week Ending"} )[Week Ending],
    MaxDate = Table.Max( Source, {"Week Ending"} )[Week Ending],
    Weeks = ( Duration.Days( MaxDate - MinDate ) / 7 ) +1,
    DateTable = Table.FromColumns(
        { List.Dates( MinDate, Weeks, #duration(7,0,0,0) ) },
        type table [Week Ending = Date.Type]
    ),
    MergedQueries = Table.NestedJoin(
        DateTable, {"Week Ending"},
        Source,    {"Week Ending"},
        "Source", JoinKind.LeftOuter
    ),
    Expanded = Table.ExpandTableColumn( MergedQueries, "Source",
        {"Item", "Available Value"}
    )
in
    Table.FillDown( Expanded, {"Item", "Available Value"} )
    // Query
let
    Source = SourceTable,
    GroupedItem = Table.Group( Source, {"Item"},
        {"ItemTable", each _,
            type table [Week Ending=nullable date, Item=nullable number, Available Value=nullable number]
        }
    ),
    RemovedItem = Table.RemoveColumns( GroupedItem, {"Item"} ),
    AddedExpand = Table.AddColumn( RemovedItem, "ExpandedItem", each
        ExpandWeeklyValue( [ItemTable] ),
        type table
    ),
    CombinedExpandedItem = Table.Combine( AddedExpand[ExpandedItem] )
in
    CombinedExpandedItem

     

    I have 960 different Items
    If you don't have external connections I would suggest you disable Background Data and Ignore the Privacy Levels under CURRENT WORKBOOK in the Query options (if not clear let me know)

    (Glad to re-create existing data)

      • Lorenzo's avatar
        Lorenzo
        Silver Contributor
        Nov 09, 2024

        TD 

        Forgot one point. If it happens you have disordered [Week Ending] by [Item] add a Sort step at the end of the query, sorting by [Item] then by [Week Ending]. Something like:

        SortedRows = Table.Sort( <previous_step_name>, {{"Item", Order.Ascending}, {"Week Ending", Order.Ascending}} )

        Reason this could happen: ExpandWeeklyValue does a Merge operation. The latter cannot guarantee an ordered set of records. This is - to my knowledge - currently undocumented. Same thing can happen with Grouping operation

Resources