Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
Looking for help in the following scenario:
I want to replace null value in the column with the average value of the column. How can I achieve this? I am interested in using conditional column or something of this sort.
Regards,
Vivek
Solved! Go to Solution.
Hi @vivran22 ,
let Source = YourTable, #"SetType" = Table.TransformColumnTypes(#"Source ",{{"Sample", type number}}), #"ReplaceAvg" = Table.ReplaceValue(#"SetType",null,List.Average(#"SetType"[Sample]),Replacer.ReplaceValue,{"Sample"}) in ReplaceAvg
Is this ok ? I assumed your initial table is YourTable and the column is named "Sample"
Tell me if there is any problem.
Regards,
Etienne
can you upload some sample data? if not, basically:
That should put the average where nulls used to be.
@Anonymous
Thank you, Nick for your response. The challenge with the resposne you shared is that it is not dynamic. If in case, there are additional rows of data, then the average value of the column will change. The need is to make it dynamic, ie using Power Query/M I can calculate the average of the column and then replaces all the null values with it instead of calculating average manually.
Regards,
Vivek
sure, DAX and M can pretty much do anything within reason. Can you upload a sample pbix file?
@Anonymous
I won't be able to share the exact data file, but following is what I am looking for:
This is the sample data:
Sample 1 |
1 |
2 |
3 |
4 |
null |
5 |
The requirement is that the null should be replaced as 3 (average of the dataset)
and in case there are addition in the table then null should update accordingly:
Sample 2 |
1 |
2 |
3 |
4 |
null |
5 |
6 |
7 |
In Sample 2, the null should be replaced as 4.
Hope this helps.
Rgds,
Vivek
Hi @vivran22 ,
let Source = YourTable, #"SetType" = Table.TransformColumnTypes(#"Source ",{{"Sample", type number}}), #"ReplaceAvg" = Table.ReplaceValue(#"SetType",null,List.Average(#"SetType"[Sample]),Replacer.ReplaceValue,{"Sample"}) in ReplaceAvg
Is this ok ? I assumed your initial table is YourTable and the column is named "Sample"
Tell me if there is any problem.
Regards,
Etienne
Hi,
How can I expand the script, to calculate the average by another column? So don't just want the average of the whole column, I want the average by another category (see below fruit type):
Fruit type | Value | average by fruit type (no need this column, its just a representation) |
apple | 1 | |
apple | 2 | |
apple | null | expected result: 1,5 |
orange | 3 | |
orange | 5 | |
orange | null | expected result: 4 |
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
102 | |
53 | |
21 | |
13 | |
11 |