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

[Julber75]

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 :

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.

[v-xulin-mstf]

Hi @Julber75,

It works well in my environment:

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.

[Julber75]

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.

[v-xulin-mstf · 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.

[Julber75]

Hi Link,

Sorry for my late answer.

Your solution works perfectly.

Thank you very much for your time and the demo.

Best regards.

Julien.

[Julber75]

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.