Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
iainshaw
Frequent Visitor

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

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 refRemove Projects with no ref

Step 2 - Create a Custom column Called Invoice - Project

Create Invoice - Project ColumnCreate 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 rowsRemove Duplicate Invoice - Project rows

Step 4 - Group by Invoice Number (Count Rows)

 

4-GroupbyInvoiceNumber.PNG

 

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

 

Expand DetailsExpand 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.

 

 

 

 

1 ACCEPTED SOLUTION

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

View solution in original post

2 REPLIES 2
iainshaw
Frequent Visitor

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.

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors