Forum Discussion

RobRayborn's avatar
RobRayborn
Helper IV
5 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, then I need to append to it  Part A, 5/1/2026, 0, and Part A, 6/1/2026, 0.... out twelve months.
When I piped this request thought Copilot it came back with:

Add12Months =
Table.TransformColumns(
#"Calculated Start of Month",

"GeneratedRows",
each
List.Transform(
{1..12}, // 12 months ahead
(i) => [
ITEM NUMBER = [ITEM NUMBER],
DUE DATE = Date.AddMonths([DUE DATE], i),
QTY DUE = 0
]
)
),

// Convert the lists into actual rows
ExpandGenerated =
Table.ExpandListColumn(Add12Months, "GeneratedRows")
> Table.ExpandRecordColumn(
_,
"GeneratedRows",
{"ITEM NUMBER", "DUE DATE", "QTY DUE"}
),

// Append original rows + generated future rows
FinalTable = Table.Combine({Source, ExpandGenerated})
in
FinalTable

Though it shows No Syntax errors have been detected in the Advance Editor, when I let the query run I receive this error:
An error occurred in the ‘’ query. Expression.Error: The name '_' wasn't recognized. Make sure it's spelled correctly.

If someone could help me figure out how to get this to work, I would greatly apprecate it. It will solve one of my biggest problems with incoming incomlete data.

 

Need Help
  • jgeddes's avatar
    jgeddes
    5 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...

     

2 Replies

  • jgeddes's avatar
    jgeddes
    Super User
    5 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...

     

  • RobRayborn's avatar
    RobRayborn
    Helper IV
    5 months ago

    Thank you so very much. You have helped to solve my issue of missing data.