Forum Discussion
Get next non blank value in column
Hi everyone,
I hope you can help.
I have a report that summarises POs for each account, and then before the next account it shows the account number and overall value.
Ideally I need to show the related account number next to each of the POs, as this joins to another table which needs summarising - the data looks like the image on the left
| PO Number | Value | Account | Overall | PO Number | Value | New Account Column | Overall | |
| O000100/01252 | 9.2 | O000100/01252 | 9.2 | 100.2011 | ||||
| 0 | 100.2011 | 9.2 | 0 | 100.2011 | 9.2 | |||
| O000100/01228 | 91.71 | O000100/01228 | 91.71 | 100.2104 | ||||
| 0 | 100.2104 | 91.71 | 0 | 100.2104 | 91.71 | |||
| O000950/02810 | 16.7 | O000950/02810 | 16.7 | 100.2211 | ||||
| 0 | 100.2211 | 16.7 | 0 | 100.2211 | 16.7 | |||
| O000100/01303 | 469 | O000100/01303 | 469 | 100.2242 | ||||
| 0 | 100.2242 | 469 | 0 | 100.2242 | 469 | |||
| O000100/01304 | 70.38 | O000100/01304 | 70.38 | 100.2334 | ||||
| 0 | 100.2334 | 70.38 | 0 | 100.2334 | 70.38 | |||
| O000100/01227 | 95 | O000100/01227 | 95 | 100.241 | ||||
| O000100/01227 | 95 | O000100/01227 | 95 | 100.241 | ||||
| O000100/01227 | 95 | O000100/01227 | 95 | 100.241 | ||||
| O000100/01298 | 290 | O000100/01298 | 290 | 100.241 | ||||
| 0 | 100.241 | 575 | 0 | 100.241 | 575 |
I have tried creating a dervived table, considered lookups (which went on forever), and hoped there would be a next non blank value, but nothing seems to work.
I have indexed the table to see if that would help, but have reached an impasse where the brain just won't function anymore.
If anyone could suggest a formula which essentially would be:
If Account<>Blank() Then Account, else equal next non blank in Account Column I would really appreciate it.
Many thanks
Hi ChrisCam ,
You can achieve this in powerquery with couple of steps, See below:
1st 4 columns is originated data last columns is desired out put data
To achieve this Go to Edit Queries -->> Transform tab --->> Click on Fill (See below img)
Thanks & Regards,
B V S S
6 Replies
Apologies the table I attached was not very clear - I need to add a new column which should reflect the below:
PO Number Value Account New Account Column Overall O000100/01252 9.2 100.2011 0 100.2011 100.2011 9.2 O000100/01228 91.71 100.2104 0 100.2104 100.2104 91.71 O000950/02810 16.7 100.2211 0 100.2211 100.2211 16.7 O000100/01303 469 100.2242 0 100.2242 100.2242 469 O000100/01304 70.38 100.2334 0 100.2334 100.2334 70.38 O000100/01227 95 100.241 O000100/01227 95 100.241 O000100/01227 95 100.241 O000100/01298 290 100.241 0 100.241 100.241 575 Hi ChrisCam ,
1st 3 Columns are your Actual data and Last 3 Columns are your desired output. Is it correct??
