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

hananak

Board Regular
Joined
Feb 10, 2022
Messages
110
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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.
 

Attachments

  • PQ Help.PNG
    PQ Help.PNG
    26.6 KB · Views: 20

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
Can we assume that when we find a zero value for a month, the previous month is the most recent month?
Or, should we consider "this" (current) month as the most recent month, find it from the header or column index (M7B or 8th column in this case), and fill the next month's values according to "this" month no matter if it has been updated yet or not? Note: In this case, all the next months' values will show as zero until this month is updated.

The first scenario sounds more applicable to me unless there is a chance an H value could be 0, so the code below is checking the first row data, finds the last non-zero month value, and uses it as the most recent month with data.
Power Query:
let
    // Use the table source for real data
    // Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8jBU0lEyMgUSxgZAgjgcqwPUaATTY2xKmkZjINMQZK2hEfEaYwE=", BinaryEncoding.Base64), Compression.Deflate)),
    {"H"} & List.Transform({1..12}, each "M" & Number.ToText(_) & "B")),
    ChangeTypes = Table.TransformColumnTypes(Source,
                    {{"H", type text}} & List.Transform(List.RemoveFirstN(Table.ColumnNames(Source)), each {_, type number})),
    LastNonZero = Table.Last(Table.SelectRows(Record.ToTable(ChangeTypes{0}), each [Value] <> 0))[Name],
    Result = Table.FromRecords(
                    Table.TransformRows(ChangeTypes,
                        (r) => Record.TransformFields(r,
                                List.Transform({2..12},
                                    (i) => {"M" & Number.ToText(i) & "B", each if _ = 0 then Record.Field(r, LastNonZero) else _}))))
in
    Result
 
Upvote 0
Dear All,

Is this even possible in Power Query?
Can we assume that when we find a zero value for a month, the previous month is the most recent month?
Or, should we consider "this" (current) month as the most recent month, find it from the header or column index (M7B or 8th column in this case), and fill the next month's values according to "this" month no matter if it has been updated yet or not? Note: In this case, all the next months' values will show as zero until this month is updated.

The first scenario sounds more applicable to me unless there is a chance an H value could be 0, so the code below is checking the first row data, finds the last non-zero month value, and uses it as the most recent month with data.
Power Query:
let
    // Use the table source for real data
    // Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8jBU0lEyMgUSxgZAgjgcqwPUaATTY2xKmkZjINMQZK2hEfEaYwE=", BinaryEncoding.Base64), Compression.Deflate)),
    {"H"} & List.Transform({1..12}, each "M" & Number.ToText(_) & "B")),
    ChangeTypes = Table.TransformColumnTypes(Source,
                    {{"H", type text}} & List.Transform(List.RemoveFirstN(Table.ColumnNames(Source)), each {_, type number})),
    LastNonZero = Table.Last(Table.SelectRows(Record.ToTable(ChangeTypes{0}), each [Value] <> 0))[Name],
    Result = Table.FromRecords(
                    Table.TransformRows(ChangeTypes,
                        (r) => Record.TransformFields(r,
                                List.Transform({2..12},
                                    (i) => {"M" & Number.ToText(i) & "B", each if _ = 0 then Record.Field(r, LastNonZero) else _}))))
in
    Result
Hi,

Thanks for your reply.

Yes, when the total column value is Zero, the previous month is the most recent month. "Can we assume that when we find a zero value for a month, the previous month is the most recent month?"

When I run your code, it works for the first time but when I update the M3B or M4B column with the actual budget values, the code does not work and nothing happens on refreshing the query. Please see the picture where I have updated M3B values highlighted in red in the first table. The result is in the second table using Power query, it did not update the values, the code is still picking the M2B values. The third table shows the output that should be.

Kindly advice...
 

Attachments

  • 1.PNG
    1.PNG
    28.8 KB · Views: 10
Upvote 0
Please delete the currently active Source line from the code and activate the previous line instead:

Power Query:
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],

Change Table1 with your table name and refresh.

As I explained in the code, I used sample data in binary format for easy testing.

It should be like below:
Power Query:
let
     Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    ChangeTypes = Table.TransformColumnTypes(Source,
                    {{"H", type text}} & List.Transform(List.RemoveFirstN(Table.ColumnNames(Source)), each {_, type number})),
    LastNonZero = Table.Last(Table.SelectRows(Record.ToTable(ChangeTypes{0}), each [Value] <> 0))[Name],
    Result = Table.FromRecords(
                    Table.TransformRows(ChangeTypes,
                        (r) => Record.TransformFields(r,
                                List.Transform({2..12},
                                    (i) => {"M" & Number.ToText(i) & "B", each if _ = 0 then Record.Field(r, LastNonZero) else _}))))
in
    Result
 
Upvote 0
let
// Use the table source for real data
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
{"H"} & List.Transform({1..12}, each "M" & Number.ToText(_) & "B")),
ChangeTypes = Table.TransformColumnTypes(Source,
{{"H", type text}} & List.Transform(List.RemoveFirstN(Table.ColumnNames(Source)), each {_, type number})),
LastNonZero = Table.Last(Table.SelectRows(Record.ToTable(ChangeTypes{0}), each [Value] <> 0))[Name],
Result = Table.FromRecords(
Table.TransformRows(ChangeTypes,
(r) => Record.TransformFields(r,
List.Transform({2..12},
(i) => {"M" & Number.ToText(i) & "B", each if _ = 0 then Record.Field(r, LastNonZero) else _}))))
in
Result

The line with "H" and after that Change Type are giving errors.
 
Upvote 0
Please copy and paste the code that I sent in my previous reply. I edited it shortly but you should have seen the initial post without the modified code.
 
Upvote 0
Please copy and paste the code that I sent in my previous reply. I edited it shortly but you should have seen the initial post without the modified code.
Thanks the code is working in the sample file correctly now.

Would you be able to advise how to fit in the code in the below code after the Append has completed?

Power Query:
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Filtered Rows" = Table.SelectRows(Source, each Text.StartsWith([Name], "M")),
    #"Filtered Hidden Files1" = Table.SelectRows(#"Filtered Rows", each [Attributes]?[Hidden]? <> true),
    #"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File", each #"Transform File"([Content])),
    #"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
    #"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File"}),
    #"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File", Table.ColumnNames(#"Transform File"(#"Sample File"))),
    #"Inserted Merged Column" = Table.AddColumn(#"Expanded Table Column1", "Merged", each Text.Combine({[#"Account Name"], "-", [#"Cost Code"], "-", [#"Acount Code"]}), type text),
    #"Renamed Columns" = Table.RenameColumns(#"Inserted Merged Column",{{"Merged", "GL"}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Renamed Columns",{"Source.Name", "Account Name", "WTEB#(lf)", "GL"}),
    #"Filtered Rows1" = Table.SelectRows(#"Removed Other Columns", each [#"Account Name"] = "AY"),
    #"Renamed Columns2" = Table.RenameColumns(#"Filtered Rows1",{{"Source.Name", "Month"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns2",{{"WTEB#(lf)", type number}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Changed Type",{"Month", "GL", "WTEB#(lf)"}),
    #"Replaced Value" = Table.ReplaceValue(#"Reordered Columns",".xlsx","",Replacer.ReplaceText,{"Month"}),
    #"Pivoted Column" = Table.Pivot(#"Replaced Value", List.Distinct(#"Replaced Value"[Month]), "Month", "WTEB#(lf)"),
    OldColNames1 = List.Skip (Table.ColumnNames(#"Pivoted Column"),2),
    NewColNames1 = List.Transform ({1.. List.Count (OldColNames1) }, (n)=> "WTEB" & Text.From (n)),
    FromTo1 = List.Zip ({OldColNames1,NewColNames1}),
    NewColNamesFinal = Table.RenameColumns(#"Pivoted Column",FromTo1),
    #"Appended Query" = Table.Combine({NewColNamesFinal, Table8})
in
    #"Appended Query"
 
Last edited by a moderator:
Upvote 0
Please copy and paste the code that I sent in my previous reply. I edited it shortly but you should have seen the initial post without the modified code.
Thank you very much. The code provided is working now.
 
Upvote 0
Glad to hear it helps.
Sorry to ask but I need to little tweaking in the code.

1) The are already two columns for Headings - Heading 1 and Heading 2.

2) For Monthly budget columns where there is no data it is blank in excel and in power query it is null values.

Please see the picture I have uploaded.

I would really appreciate for your guidance.

Many Thanks.
 

Attachments

  • 1.PNG
    1.PNG
    122.6 KB · Views: 12
Upvote 0

Forum statistics

Threads
1,223,099
Messages
6,170,107
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top