cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
F75
Helper I
Helper I

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

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
v-frfei-msft
Community Support
Community Support

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.

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

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

Nolock
Resident Rockstar
Resident Rockstar

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
November 2022 Update

Check it Out!

Click here to read more about the November 2022 updates!

Power BI Dev Camp Session 27

Ted's Dev Camp

This session walks through creating a new Azure AD B2C tenant and configuring it with user flows and custom policies.

Health and Life Sciences Power BI User Group

Health and Life Sciences Power BI User Group

Power BI specialists at Microsoft have created a community user group where customers in the provider, payor, pharma, health solutions, and life science industries can collaborate.

Top Solution Authors
Top Kudoed Authors