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
SDittmannFleet
Advocate III
Advocate III

Power Query vs. Aggregating in Power BI: Issue or bug?

So, I am not sure what I'm missing here, but it may just be a difference in how Power Query and Power BI aggregate data. I have a table with the fields "Username", "FirstName", "LastName", "LoginDateTime" and "Account".

 

When I "Group By" in Power Query by all but the "LoginDateTime" field and then put in a filter of "< 5" on the resulting "Count" field, I get 81 records.

 

If I take the query I am referencing in Power Query and build the same table visual and put a visual filter on ("< 5"), I get 69 records.

 

Why would that be?

1 ACCEPTED SOLUTION

With hundredths of a second apart it will depend on the format used and you may need to convert it to use it the visual correctly. The solution here might help:

 

https://community.fabric.microsoft.com/t5/Desktop/Convert-seconds-column-into-duration-column-hh-mm-...

 

Power Query will work a lot better with this without any additional formatting for a visual so i'd recommend using the aggregation there.

 

If I answered your question, please mark my post as solution, Appreciate your Kudos 👍

View solution in original post

9 REPLIES 9
SDittmannFleet
Advocate III
Advocate III

I can't post any sample output w/o violating privacy. That said, please just trust that Power BI is leaving out records for no discernible reason.

SDittmannFleet
Advocate III
Advocate III

What is left out seems to be arbitrary, at least to me.

DOLEARY85
Super User
Super User

Hi,

 

Does the visual table have some summarization on any of the fields? Is it possible to see the data or PBIX file?

 

I would always use the Power Query data if you aggregating it there, if only for the for performance.

 

If I answered your question, please mark my post as solution, Appreciate your Kudos 👍

NaveenGandhi
Super User
Super User

Hello @SDittmannFleet 

 

Table visual will only show unique rows, that might be reason for your issue. Can you check which rows are present in power query and not in table visual.

 

Regards,

Naveen

 

If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

 

SDittmannFleet
Advocate III
Advocate III

Also, if there is a difference and I have to live with it, which one do I go with?

I'd start by comparing the lists to see which ones are missing, i'd imagine that the unique datetime field should make it so that there's no duplicate records?

 

Once you've identified which are missing you'll be able to either figure out what's causing the issue or at least know which is correct to decide which to go with. I'd guess that Power Query is the accurate one but without seeing the data and what's happening it's a best guess.

 

If I answered your question, please mark my post as solution, Appreciate your Kudos 👍

These are the first 20 entries. Names are fake and emails redacted, but the "Lookup" column reflects a quick MATCH between what the aggregated table produces in Power Query and what I get when I use the aggregation in the visual (count distinct) and then filter the visual to give me anybody with a logon count of < 5. The only thing I can think of that causes this is that Power BI for some reason cannot discern between hundredths of a second when doing the count. The entries that are missing are people that have logons literally one right after the other, a hundredth of a second apart. Thoughts?

+-------------+--------------+-------------------------+-------+--------+
| First Name  |  Last Name   |        Username         | Count | Lookup |
+-------------+--------------+-------------------------+-------+--------+
| Allen       | Flores       | someuser@somedomain.com |     3 | #N/A   |
| Alyssa      | Poole        | someuser@somedomain.com |     1 | 1      |
| Bobbi       | Karpus       | someuser@somedomain.com |     1 | #N/A   |
| Brandie     | Rucker       | someuser@somedomain.com |     1 | 2      |
| Brent       | Strachan     | someuser@somedomain.com |     1 | #N/A   |
| Brian       | Olesko       | someuser@somedomain.com |     3 | 4      |
| Bruce       | Marshall     | someuser@somedomain.com |     3 | 5      |
| Bryan       | Holt         | someuser@somedomain.com |     1 | #N/A   |
| Bryan       | Stuehrenberg | someuser@somedomain.com |     2 | #N/A   |
| Cami        | Hunt         | someuser@somedomain.com |     1 | 6      |
| Chris       | Ginest       | someuser@somedomain.com |     1 | 7      |
| Christopher | Tulga        | someuser@somedomain.com |     3 | 8      |
| Courtney    | Chafer       | someuser@somedomain.com |     2 | 9      |
| Courtney    | Watson       | someuser@somedomain.com |     1 | #N/A   |
| Dana        | Bower        | someuser@somedomain.com |     1 | 10     |
| Daniel      | Jasak        | someuser@somedomain.com |     3 | 12     |
| Daniel      | Kozlowski    | someuser@somedomain.com |     4 | 13     |
| Daryl       | Marske       | someuser@somedomain.com |     3 | 14     |
| David       | Lee          | someuser@somedomain.com |     2 | 15     |
| Derek       | Russell      | someuser@somedomain.com |     4 | 16     |
+-------------+--------------+-------------------------+-------+--------+

 

With hundredths of a second apart it will depend on the format used and you may need to convert it to use it the visual correctly. The solution here might help:

 

https://community.fabric.microsoft.com/t5/Desktop/Convert-seconds-column-into-duration-column-hh-mm-...

 

Power Query will work a lot better with this without any additional formatting for a visual so i'd recommend using the aggregation there.

 

If I answered your question, please mark my post as solution, Appreciate your Kudos 👍

The dates are all formatted as "General Date" or "Date/time". I'll simply keep this at the back of my head that there may be issues and assume that what I can do in Power Query takes precedence. Thanks!

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.