cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
F75 Regular Visitor
Regular Visitor

Copy last period data to future periods

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

 

image.png

1 ACCEPTED SOLUTION

Accepted Solutions
Super User II
Super User II

Re: Copy last period data to future periods

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

View solution in original post

4 REPLIES 4
Community Support Team
Community Support Team

Re: Copy last period data to future periods

Hi @F75 ,

 

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

 

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.
Super User II
Super User II

Re: Copy last period data to future periods

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

View solution in original post

F75 Regular Visitor
Regular Visitor

Re: Copy last period data to future periods

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

Super User II
Super User II

Re: Copy last period data to future periods

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")

?

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors
Top Kudoed Authors