Forum Discussion
Fill up only one row
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?
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 RegardsCommunity 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.
4 Replies
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".
Seems the steps would be:
Filter the data for NULL Forecast, then aggragate to get the max date. Seems simple, what am I missing?
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 RegardsCommunity 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.
