Inconsistent results when grouping. Works in Power Query in Excel, not in Power Query in PBI

[iainshaw]

I'm getting a strange result in Power Query.  I was hoping writing it down and illustrating it with screenshots would make me realise what I'm doing wrong but I've got a Power Query that works in Excel and doesn't work in Power BI's Power Query although I am performing the same steps on each.  I would appreciate some help in identifying the problem.

The source data is a table of InvoiceItems from Sage (c. 13k rows).  I'm loading it from Azure SQL.  Each row refers to a particular line item on an invoice.  An invoice can have multiple line item.  Each line item can be assigned to a Project.  There are fields for InvoiceID and ProjectID. 

I'm trying to produce a table of Invoice - Projects so I can identify Invoices that have reference more than one project.  My query does that but it brings back one record that I can see is wrong.

I bring the data into Power Query and then run these steps.

Step 1.  Remove all rows that don't have a Project Ref (0s in Project_ID)

Remove Projects with no ref

Step 2 - Create a Custom column Called Invoice - Project

Create Invoice - Project Column

Step 3 - Remove duplicate rows in Invoice - Project. I don't need details, I just need the fact that the Invoice - Project combination exists.

Remove Duplicate Invoice - Project rows

Step 4 - Group by Invoice Number (Count Rows)

Step 5 - Expand the Details column to bring back Project_ID and Invoice - Project

Expand Details

I then load the Query into Power BI.   I can see I've got 6 invoices that reference multiple projects.  Everything looks fine apart from one record that isn't right.  Invoice 4061 reports having line items beloning to two projects.  It doesn't, it only has one line item.  The incorrect project is actually referenced in the previous invoice 4060 which may or may not be coincedence.  The source data is accurate, the grouped data is inaccurate.  The same steps in Power Query in Excel return the correct results. 

Please can someone help.  This is driving me crazy.  Thank you.

[iainshaw]

Just to be a bit more clear on the error, I end up with a Grouped Invoice - Project combination that does not exist in the original data.  I can't see how the steps I am performing can do that.

[iainshaw · Accepted solution]

I am sorry.  I have found a difference in the source data. The methodology described above works perfectly.  I am an idiot.