GROUP BY in PowerQuery ignores 1 specific value

[sidneyjacobs]

Hey everybody,

I'm either missing something extremely obvious, or I've come across a bug in PowerQuery.

I'm creating a table of clients, their orders and the products in those orders. While trying to use a Group By of all clients and their last orders, 1 clientnumber dissapears.

To test if I've done anything wrong, this was my debugging process:

1: From the current state, I filter the column clientnumber to CONTAINS '#' (the missing client). This shows me 1 line with the client and their order.

2: I now use the Group By function on the column clientnumber without the filter. As aggregation I used count rows. If I filter the resulting table on clientnumber, again with CONTAINS '#'. This gives a table with no entries, meaning that the value that was there before is not included in the Group By.

3: To double check, I used groupby on the filtered table (so 1 row) and then if obviously works.

So the groupby only includes this specific client if I filter the data to only include their clientnumber. If I do not add this filter, they are skipped.

Am I missing something obvious? Sorry for not including screenshots/actual values, since it is confidential information. If needed, I can try to obscure parts and include them

Kind regards,

Sidney

[Anonymous]

That is strange then!  Maybe still try replacing that value with ABABAB and then see if it groups.  You can always change it back after the grouping step.

[Anonymous]

I bet it's an escape character issue, with # being an escape.  Before grouping, try using the Replave Values function on the Client Number column, and replace # with ~ and then group.

--Nate

[sidneyjacobs]

Hello Nate,

That's a good suggestion. I was a little unclear however: I used # to replace the actual value (think A0505483249) This is a mix of letters and numbers, never containing an escape character.

[v-kelly-msft]

Hi  @sidneyjacobs ,

Could you pls paste some screenshots to let us check whether there is some missing details?

Remember to remove the confidential information.

Best Regards,
Kelly

Did I answer your question? Mark my raeply as a solution!

[sidneyjacobs · Accepted solution]

Dear Kelly,

I'm still not quite sure why.. but after a few database loads and recreating the steps my problem went away. I guess I'm better off removing the topic since I can't reproduce the steps and pose a solution for other community members.
Thank you for reaching out though!

Sidney

[v-kelly-msft]

Hi  @sidneyjacobs ,

If so,you could mark your reply as answered to close it.

Best Regards,
Kelly

Did I answer your question? Mark my raeply as a solution!