Pick data from the most recent Month and replicate it in future months

[hananak]

Dear All,

I need quick help from experts in Power Query.

Currently, I have 12 budget columns, each month I receive Budget data and the relevant columns are populated with the budgeted figures and for future months it remains blank.
What I want are the columns I have for future months where the data is not available to pick the data from the most recent month's budget data and populate the future months' columns.

Please see the picture I have uploaded that will help you to understand better, what I want to do.

The columns highlighted in Green mean the Actual Budget data and the one Highlighted in Yellow is the data picked from the most recent actual data available.

Your help would be really appreciated.

Thank you in advance.

 

[smozgur]

Sure. You can use the following simplified version.

Note: Since the empty values are null instead of zero, there is no need to change data types for this portion. You can adjust the data types later if necessary.
In this version, the only constraint is the WTEB prefix for the adjacent month columns. It also doesn't matter how many Heading columns exist.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    LastNoBlank = Table.Last(Table.SelectRows(Record.ToTable(Source{0}), each [Value] <> null))[Name],
    Result = Table.FromRecords(
                    Table.TransformRows(Source,
                        (r) => Record.TransformFields(r,
                                List.Transform({2..12},
                                    (i) => {"WTEB" & Number.ToText(i), each if _ = null then Record.Field(r, LastNoBlank) else _}))))
in
    Result

 

[hananak]

Sure. You can use the following simplified version.

Note: Since the empty values are null instead of zero, there is no need to change data types for this portion. You can adjust the data types later if necessary.
In this version, the only constraint is the WTEB prefix for the adjacent month columns. It also doesn't matter how many Heading columns exist.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    LastNoBlank = Table.Last(Table.SelectRows(Record.ToTable(Source{0}), each [Value] <> null))[Name],
    Result = Table.FromRecords(
                    Table.TransformRows(Source,
                        (r) => Record.TransformFields(r,
                                List.Transform({2..12},
                                    (i) => {"WTEB" & Number.ToText(i), each if _ = null then Record.Field(r, LastNoBlank) else _}))))
in
    Result

Perfect. Thank you very much!

I really appreciate the time and effort you have put into resolving my query.

 

[smozgur]

Perfect. Thank you very much!

I really appreciate the time and effort you have put into resolving my query.

You're welcome.