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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
Julber75
Frequent Visitor

Issue with the "Group By" and the "Append Queries" functions in Power Query (Excel & Power BI).

When I use the "Group By" function on a single query (corresponding to one source of data) to obtain the average value, the value is calculated based on the number of values.

 

For exemple for a file with 2 lignes of data per date, grouping by dates presentes :
- If each ligne has a value, the average of the 2 values ((value one + value two) / 2 - the number of values available).
- But if one ligne is empty for that date (null value) the average value presented is the only value available since it's the value divided by 1 ((value available) / 1 - the number of values available).

 

When I use the Append Queries function (corresponding to a second source of data) to add a third ligne of data per date, the Group By function doesn't work the same way to calculate the average value.

Whatever the number of values available per date, it always divide the result per 3.

- If there are 3 values available, that's ok.

- But if only 2 or 1 value is availbale the result gets divided by 3 thus providing a very different result than previously.

 

Since I can't upload files, here is a picture of what I'm trying to describe :

Capture.JPG

 

What is the problem ? is it a bug ?
What should be the correct way (normal behaviour) for the Group By function to calculate the average values (by the number of values available or by the number of the Group By references) ?
I'm looking for the first way of calculating the values (i.e. calculate the average value by dividing with the number of values available), what should I do ?

 

Regards.

 

Julien.

1 ACCEPTED SOLUTION

Hi @Julber75

 

You can try this:

 

= Table.Group(Source, {"Date"}, {{"Count", each List.Average(List.Select([Value A], each _<> "" and _ <> null))}})
= Table.Group(Source, {"Date"}, {{"Count", each List.Average(List.Select([Value B], each _<> "" and _ <> null))}})
= Table.Group(Source, {"Date"}, {{"Count", each List.Average(List.Select([Value C], each _<> "" and _ <> null))}})
= Table.Group(Source, {"Date"}, {{"Count", each List.Average(List.Select([Value D], each _<> "" and _ <> null))}})

 

Here is the demo, please try it.

 

Best Regards,

Link 

 

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

View solution in original post

5 REPLIES 5
v-xulin-mstf
Community Support
Community Support

Hi @Julber75,

 

It works well in my environment:

v-xulin-mstf_0-1615789623819.png

v-xulin-mstf_1-1615789643399.png

It excludes null values in the calculation of the average.

Maybe you can also create measures to meet your need.

Such as:

Agerage A=
CALCULATE(
    AVERAGE('Table'[valueA]),
    FILTER(
        'Table',
        'Table'[Date]=MAX('Table'[Date]) && 'Table'[valueA] <>BLANK()
    )
)

 

Best Regards,

Link

 

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

 

Hi Link,

 

Thank you for your interrest in my problem and your answser.

I can't use measures for my problem, since I apply many more transformations to the data after calculating the average values.

 

Here is a link to the files of my exemple (2 .csv and 1 .xlsx)

https://1drv.ms/u/s!AmDpICZO-uXPgaJrQpzcO6cy22lKVQ

/!\ You will have to change the path to the .csv files in the Power Query requests in order for them to work on your environement (I'm sure you know that but, just in case  😉 ).

 

Would you be kind enough to tell me what I'm doing wrong?

 

Best regards.

 

Julien.

 

Hi @Julber75

 

You can try this:

 

= Table.Group(Source, {"Date"}, {{"Count", each List.Average(List.Select([Value A], each _<> "" and _ <> null))}})
= Table.Group(Source, {"Date"}, {{"Count", each List.Average(List.Select([Value B], each _<> "" and _ <> null))}})
= Table.Group(Source, {"Date"}, {{"Count", each List.Average(List.Select([Value C], each _<> "" and _ <> null))}})
= Table.Group(Source, {"Date"}, {{"Count", each List.Average(List.Select([Value D], each _<> "" and _ <> null))}})

 

Here is the demo, please try it.

 

Best Regards,

Link 

 

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

Hi Link,

 

Sorry for my late answer.

Your solution works perfectly.

 

Thank you very much for your time and the demo.

 

Best regards.

 

Julien.

Hi Link,

 

Thank you for your answer and the demo.

I will try it as soon as I can (lots of work) and let you know.

 

Best regards.

 

Julien.

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

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