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.
I want to replace null value in the column with the average value of previous value and next value ,. How can I achieve this?
Solved! Go to Solution.
Hi @YasserGaber ,
If there are no consecutive null values, try this calculated column.
Column =
VAR _value = 'Table'[Value]
VAR _avg =
AVERAGEX (
FILTER (
'Table',
'Table'[Index]
>= EARLIER ( 'Table'[Index] ) - 1
&& 'Table'[Index]
<= EARLIER ( 'Table'[Index] ) + 1
),
'Table'[Value]
)
VAR _result =
IF ( 'Table'[Value] <> BLANK (), 'Table'[Value], _avg )
RETURN
_result
Attached PBIX file for reference.
Best Regards,
Gao
Community Support Team
If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!
How to get your questions answered quickly -- How to provide sample data
Hi @YasserGaber ,
If there are no consecutive null values, try this calculated column.
Column =
VAR _value = 'Table'[Value]
VAR _avg =
AVERAGEX (
FILTER (
'Table',
'Table'[Index]
>= EARLIER ( 'Table'[Index] ) - 1
&& 'Table'[Index]
<= EARLIER ( 'Table'[Index] ) + 1
),
'Table'[Value]
)
VAR _result =
IF ( 'Table'[Value] <> BLANK (), 'Table'[Value], _avg )
RETURN
_result
Attached PBIX file for reference.
Best Regards,
Gao
Community Support Team
If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!
How to get your questions answered quickly -- How to provide sample data
hi @YasserGaber - using Power Query, you can add 3 index columns. the 1st starts at zero, 2nd starts at 1, and then 3rd starts at 2. At this point use Table.Buffer(). This buffered table can be joined twice. The first join is between the 2nd index ( row x ) and 1st Index ( i.e. row x +1 ), the second join is between 2nd Index ( row x ) and the 3nd Index (i.e. row x - 1). You can now expand to have 3 amount columns - Current Row Value, Next Row Value and Previous Row Value. Then have an If statement like:
if [Current Row Value] <> null then [Current Row Value] else List.Average( { [Next Row Value] , [Previous Row Value] } )
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 |
---|---|
111 | |
97 | |
80 | |
69 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |