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.
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?
Solved! Go to 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.
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".
Proud to be a Super User! | |
Seems the steps would be:
Filter the data for NULL Forecast, then aggragate to get the max date. Seems simple, what am I missing?
Proud to be a Super User! | |
@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.
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.
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.