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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
SDittmannFleet
Advocate IV
Advocate IV

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 IV
Advocate IV

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 IV
Advocate IV

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 IV
Advocate IV

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
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors