Fill up only one row

[Michieldo]

Hi,

I have a column with in-sample data and a column with forecasts. The table is formatted as follows:

Date	In-sample	Forecast
1-1-2022	100	null
1-2-2022	120	null
1-3-2022	120	null
1-4-2022	null	130
1-5-2022	null	130

I would like the fill up the forecast only by 1 row. My desired result is the following:

Date	In-sample	Forecast
1-1-2022	100	null
1-2-2022	120	null
1-3-2022	120	130
1-4-2022	null	130
1-5-2022	null	130

What is the most efficient way in Power Query?

[ToddChitt]

What is your back-end data source? If it is SQL, your GROUP BY in Power Query *may* get pushed down into the native T-SQL statement that Power Query issues to the server. If so, such grouping is done by SQL instead of the Power BI mashup engine. Pretty nice. If you are not sure, investigat the topic of "Query folding in Power Query".

[ToddChitt]

Seems the steps would be:

Filter the data for NULL Forecast, then aggragate to get the max date. Seems simple, what am I missing?

[Michieldo]

@ToddChitt Thanks for your reply, that is indeed a possibility. However, I forgot to mention that I have to do this for over a set of 10000 products (>1M rows) so I was looking for a less memory intensive method.

[v-chenwuz-msft · Accepted solution]

Hi @Michieldo ,

You can try edit the code for if the next row is null and the current row is not null then get the next row's forecast else your forecast expression. Before this, add an index column firstly.

if #"Added Index"{[Index]+1} [In-sample] = null & [In-sample] <> null then #"Added Index"{[Index]+1} [Forecast] else [Forecast]

About how to get the next row values ,you can refer this article.

https://www.myonlinetraininghub.com/referencing-next-row-power-query 

Best Regards

Community Support Team _ chenwu zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.