How can I create a column in a power query that inserts a formula into table after you close and load the power query?

Power Query is not Excel. SUMIFS is not a Power Query function. What exactly are you trying to achieve in PQ?

Dear @Cynthia McNeill,

Good day! Thank you for posting your question in the Microsoft Q&A forum.

We apologize for any inconvenience you may encounter when using our services/ products. Based on your description:

The error message "Invalid identifier" arises from the way Power Query processes formulas. Unlike Excel, Power Query does not support Excel-style functions such as =SUMIFS(...) within its custom column editor.

To effectively achieve your objective, consider the following alternative methods:

After loading the data from Power Query into Excel, you can generate a TotQty column that replicates the behavior of the SUMIFS formula by following the steps outlined below.

Option 1: Add the Formula in Excel After Load

1.     Load your Power Query table into Excel.

2.     In Excel, add a new column next to your table.

3.     Enter the formula:

=SUMIFS([Qty],[Item ID],[@[Item ID]],[Name],[@Name])

4.     Excel will automatically fill this formula down the column if your table is formatted as a Table (which Power Query does by default).

Option 2: Use Power Query Grouping Instead

If you want to calculate the total quantity per Item ID and Name within Power Query, you can:

1. Go to Transform Data.
2. Select both Item ID and Name columns.
3. Click Group By.
4. In the dialog:
   • Group by: Item ID and Name
   • New column name: TotQty
   • Operation: Sum
   • Column: Qty
5. Merge this grouped table back with your original table using a merge query on Item ID and Name.

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment". 

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

you can also test out a one-liner

(Source is the previous step name or any step name that has a table with the 3 required columns in (as long as it doesn't cause a circular reference).)

= Table.AddColumn(Source, "Sum If", each ((ID,Nm)=>List.Sum(Table.SelectRows(Source, each ([Item ID] = ID) and ([Name] = Nm))[Qty]))([Item ID],[Name]))

The 2 references to Source don't both have to refer to the same step, eg. it would be quite normal to have the likes of:

= Table.AddColumn(PreviousStep, "Sum If", each ((ID,Nm)=>List.Sum(Table.SelectRows(Source, each ([Item ID] = ID) and ([Name] = Nm))[Qty]))([Item ID],[Name]))

where PreviousStep is just that and Source could refer to the first step in the query (better too if that Source step includes Table.Buffer in it).

On an Excel sheet it looks like: