Forum Discussion

RobRayborn's avatar
RobRayborn
Helper IV
4 months ago
Solved

Power Query Add rows of zero amount

I have a need to add rows of data of a zero quantity.  I have three columns PART, DATE, QTY. For each row add one month to the DUE DATE twelve times, so if the original data is Part A, 4/1/2026, 26,...
  • jgeddes's avatar
    4 months ago

    Here is a sample code that uses similar logic to your example.

    let
        Source = 
        Table.FromRows(
            Json.Document(
                Binary.Decompress(
                    Binary.FromText("i45WCkgsKlFwVNJRMtE31DcyMDIDMoFErA5UygnIN0ZImZsrxcYCAA==", BinaryEncoding.Base64), 
                    Compression.Deflate
                )
            ), 
            let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Part = _t, Date = _t, Qty = _t]
        ),
        #"Changed Type" = 
        Table.TransformColumnTypes(
            Source,
            {
                {"Part", type text}, {"Date", type date}, {"Qty", Int64.Type}
            }
        ),
        generate_records = 
        Table.AddColumn(
            #"Changed Type", 
            "next12Months", 
            each 
            List.Transform(
                {0..12}, 
                (r)=> [
                    Part=[Part], 
                    Date=Date.AddMonths([Date], r), 
                    Qty=if r = 0 then [Qty] else 0
                ]
            ), 
            type list
        ),
        remove_columns = 
        Table.RemoveColumns(
            generate_records,
            {"Part", "Date", "Qty"}
        ),
        expand_lists = 
        Table.ExpandListColumn(
            remove_columns, 
            "next12Months"
        ),
        expand_records = 
        Table.ExpandRecordColumn(
            expand_lists, 
            "next12Months", 
            {"Part", "Date", "Qty"}, 
            {"Part", "Date", "Qty"}
        ),
        set_types = 
        Table.TransformColumnTypes(
            expand_records,
            {
                {"Part", type text}, {"Date", type date}, {"Qty", Int64.Type}
            }
        )
    in
        set_types

    Here I start with example table...

    and end with...