populate a column from a previous value

Hi jerryr125 ,

To do this in Power Query, please try the following code. Make sure to replace the Source with your actual table source:

let
    Source = PlaceYourSourceHere,
    SplitDate = Table.SplitColumn(Source, "Year-Month", Splitter.SplitTextByDelimiter("/", QuoteStyle.Csv), {"Month", "Year"}),
    ChangedTypes = Table.TransformColumnTypes(SplitDate, {{"Month", Int64.Type}, {"Year", Int64.Type}, {"Quantity", Int64.Type}}),
    AddDate = Table.AddColumn(ChangedTypes, "Date", each #date([Year], [Month], 1), type date),
    PreviousTable = Table.SelectColumns(AddDate, {"ProductID", "Date", "Quantity"}),
    RenamedPrev = Table.RenameColumns(PreviousTable, {{"Date", "PriorDate"}, {"Quantity", "PriorQuantity"}}),
    AddShiftedDate = Table.AddColumn(AddDate, "PriorDate", each Date.AddMonths([Date], -1), type date),
    MergedTables = Table.NestedJoin(AddShiftedDate, {"ProductID", "Date"}, RenamedPrev, {"ProductID", "PriorDate"}, "PriorData", JoinKind.LeftOuter),
    Expanded = Table.ExpandTableColumn(MergedTables, "PriorData", {"PriorQuantity"}, {"prior-year-month-month-quantity"})
in
    Expanded

If this helped, please mark it as the solution so others can benefit too. And if you found it useful, kudos are always appreciated.

Thanks,

Samson

Connect with me on LinkedIn

Check out my Blog

Going to the European Microsoft Fabric Community Conference? Check out my Session

And another possible solution..

Staring with: (note that the dates are provided as a string).

Using this:

let
    Source = ProductQuantityByMonth,  // replace with your table
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ProductID", type text}, {"Year-Month", type date}, {"Quantity", Int64.Type}}),  // The change type interprets the date string as an actual date
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Last-Year-Month Quantity", each  // we add a new column
            let 
                last_year = Table.SelectRows(#"Changed Type",     // get the previous month's row
                    (row) => // a funtion with the current row as a parameter
                        row[ProductID] = [ProductID] // for the same product
                        and row[#"Year-Month"] = Date.AddYears([#"Year-Month"],-1))  // for the previous year
                        [Quantity]  // but only the column [Quantity]
                in 
                    List.SingleOrDefault(last_year)  //  returns the first and only element of the list, null when empty or generarate an error if there are more. You can change this to a List.Sum() if the same month can occur more than once for the same product.
        )
in
    #"Added Custom"

Producing this: