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 have null values in my column identified as 999.9. I need to replace them with the average of the row above and the row below. In some circumstances like shown below, the row below is also a null value. In that case I need it to keep searching down until it comes to a value other than 999.9 and then use that to average with the row above. In this example:
1. I need a formula that for row 286 will average rows 285 and 289 getting 1.6.
2. Then the formula would averave rows 286 (which is now 1.6) and row 289 getting 2.4 for row 287.
3. Then the formula would average rows 287 (which is now 2.4) and row 289 getting 2.7 for row 288.
Final product would be:
Row 286 = 1.6
Row 287 = 2.4
Row 288 = 2.7
Any help would be much appreciated.
Solved! Go to Solution.
Hi @ven853,
To be honest, it's hard to achieve it using Power Query. I would suggest you leverage the power of Python and R. I created a demo solution with both Python and R. You can choose the best one that suits you. You can download it from the attachment.
# 'dataset' holds the input data for this script def find_next(excluded, ds): for item in ds: if item != excluded: return item return 0 result = [] column1 = dataset.iloc[:, 0] for index in range(len(column1)): if column1[index] != 999.9: result.append(column1[index]) else: next = find_next(999.9, column1[index + 1:]) result.append((next + result[-1]) / 2) dataset["new"] = result
# 'dataset' holds the input data for this script find_next <- function(excluded, ds) { for (item in ds[,1]) { if (item != excluded) { return(item) } } return(0) } result <- c() ds_length <- nrow(dataset) for (index in 1: ds_length) { if (dataset[index, 1] == 999.9) { result[index] <- (tail(result, 1) + find_next(999.9, tail(dataset, -index))) / 2.0 } else{ result[index] <- dataset[index, 1] } } final <- data.frame(result)
Best Regards,
Dale
Hi @ven853,
To be honest, it's hard to achieve it using Power Query. I would suggest you leverage the power of Python and R. I created a demo solution with both Python and R. You can choose the best one that suits you. You can download it from the attachment.
# 'dataset' holds the input data for this script def find_next(excluded, ds): for item in ds: if item != excluded: return item return 0 result = [] column1 = dataset.iloc[:, 0] for index in range(len(column1)): if column1[index] != 999.9: result.append(column1[index]) else: next = find_next(999.9, column1[index + 1:]) result.append((next + result[-1]) / 2) dataset["new"] = result
# 'dataset' holds the input data for this script find_next <- function(excluded, ds) { for (item in ds[,1]) { if (item != excluded) { return(item) } } return(0) } result <- c() ds_length <- nrow(dataset) for (index in 1: ds_length) { if (dataset[index, 1] == 999.9) { result[index] <- (tail(result, 1) + find_next(999.9, tail(dataset, -index))) / 2.0 } else{ result[index] <- dataset[index, 1] } } final <- data.frame(result)
Best Regards,
Dale
FYI, this will be very difficult if not impossible in DAX as it requires recursion.
Recursion is possible in the Power Query M Language though as seen here:
https://www.thebiccountant.com/2017/09/26/recursion-m-beginners/
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 |
---|---|
114 | |
99 | |
82 | |
70 | |
61 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |