Copy last period data to future periods

[F75]

I want to copy the last month Actuals (2019.005) data to Budget periods (2019.006 thru 2019.012) , how do i do it in Power Query? thanks

[v-frfei-msft]

Hi @F75 ,

Based on my test, it is hard to achieved by power BI.

[Nolock · Accepted solution]

Hi @F75,

I have a solution for you. It's a little bit complicated, but it works. I have simplified the table just to 4 months but it'll work also with more months.

The main idea is to have only the first half of the table at the beginning, creating a modified copy of the origin, and combine it with the origin table.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WckwuKU3MUdJRcgTi0GAgYWhkDCSNjE2AJAjF6iCpcoKpMjYxBZImpmYwVbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Scenario = _t, Product = _t, Region = _t, #"2019.001" = _t, #"2019.002" = _t, #"2019.003" = _t, #"2019.004" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Scenario", type text}, {"Product", type text}, {"Region", type text}, {"2019.001", Int64.Type}, {"2019.002", Int64.Type}, {"2019.003", type text}, {"2019.004", type text}}),

    fnAddBudgetTable = (tbl as table, columnToCopy as text) as table =>
        let
            // create a copy of origin table with an Index column and columns Product and Region
            tblWithIndex = Table.AddIndexColumn(Table.SelectColumns(tbl, {"Product", "Region"}), "IndexForJoin"),

            // get column names of origin table
            columnNameList = Table.ColumnNames(tbl),
            // column to replicate
            dataOfColumnToCopy = Table.Column(tbl, columnToCopy),
            // count of columns in origin table
            countOfColumns = List.Count(columnNameList),
            // count of columns before the column which you want to copy
            countOfColumnsBeforeColumnToCopy = List.PositionOf(columnNameList, columnToCopy),
            // count of columns after the column which you want to copy
            countOfColumnsAfterColumnToCopy = countOfColumns - countOfColumnsBeforeColumnToCopy - 1,

            // create new rows (first nulls, then repeat one column)
            newRows = List.Transform(dataOfColumnToCopy, each 
                List.Combine(
                    {
                        List.Repeat({null}, countOfColumnsBeforeColumnToCopy + 1), 
                        List.Repeat({_}, countOfColumnsAfterColumnToCopy)
                    }
                )
            ),
            // create a table from rows
            newTable = Table.FromColumns(newRows),
            // transpose
            transposedTable = Table.Transpose(newTable),
            
            // rename generic column names to origin ones
            listOfRenamedColumns = List.Zip({Table.ColumnNames(transposedTable), columnNameList}),
            tableWithHeaders = Table.RenameColumns(transposedTable, listOfRenamedColumns),
            // remove columns Scenarion, Product, and Region
            tableWithoutScenarioAndProductAndRegion = Table.RemoveColumns(tableWithHeaders, {"Scenario", "Product", "Region"}),
            // create an index
            tableWithoutScenarioAndProductAndRegionWithIndex = Table.AddIndexColumn(tableWithoutScenarioAndProductAndRegion, "IndexForJoin2"),

            // join copy of origin table with our new table and expand columns
            joinedTable = Table.NestedJoin(tblWithIndex, "IndexForJoin", tableWithoutScenarioAndProductAndRegionWithIndex, "IndexForJoin2", "BudgetTable"),
            expandJoinedTabl = Table.ExpandTableColumn(joinedTable, "BudgetTable", Table.ColumnNames(tableWithoutScenarioAndProductAndRegionWithIndex)),
            // remove index columns
            removeIndexColumns = Table.RemoveColumns(expandJoinedTabl, {"IndexForJoin", "IndexForJoin2"}),
            // add a scenario column with value "Budget"
            addBudgetScenarionColumn = Table.AddColumn(removeIndexColumns, "Scenario", each "Budget"),

            // combine 2 tables together
            combineWithOrigin = Table.Combine({tbl, addBudgetScenarionColumn})
        in
            combineWithOrigin,
    
    // add budget rows depending on column name in the second parameter
    result = fnAddBudgetTable(#"Changed Type", "2019.002")
in
    result

[F75]

Thanks @Nolock . It works. Can i pass the coloumn index as varaiable to copy the data? 

[Nolock]

Hi @F75,

do you want to pass an index of a column instead of a name of a column?

Something like (where 2019.02 were the name of the 5th column?)

result = fnAddBudgetTable(#"Changed Type", 5)

instead of 

result = fnAddBudgetTable(#"Changed Type", "2019.002")

?