Get next non blank value in column

[ChrisCam]

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

[Anonymous]

Hi @ChrisCam ,

Can you please share your desired output

Thanks & Regards,

B V S S

[ChrisCam]

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

[Anonymous]

Hi @ChrisCam ,

1st 3 Columns are your Actual data and Last 3 Columns are your desired output. Is it correct??

[ChrisCam]

Hi,

The originating data is [PO Number]; [Value]; [Account] and [Overall]

The desired output is the column called "New Account Column"

Thanks

[Anonymous · Accepted solution]

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

[ChrisCam]

This is fantastic - thank you ever so much for such a quick response, and making me aware of a feature I did not even know existed