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]

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.

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

 

[hananak]

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.

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...

 

[smozgur]

Please delete the currently active Source line from the code and activate the previous line instead:

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:

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

 

[hananak]

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.

 

[smozgur]

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.

 

[hananak]

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?

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"

 

[hananak]

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.

 

[smozgur]

Thank you very much. The code provided is working now.

Glad to hear it helps.

 

[hananak]

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.