Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
F75
Advocate I
Advocate 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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors