Add 2 columns based on value of other columns

[shellz]

I want to group by the Code, adding columns (Projection1 + Projection2) based on the values of Year1 & Year2. Is this possible in Power Query? I'm fairly new to power query and have been trying to do this with custom columns.

Data


I'd like to transform the data to look like

 

[MarcelBeug]

You can split the table in 2 parts, rename columns and combine both table parts so you have 1 table with columns Code, Year and Projection.
Now you can make some adjustments to the "Year" column and pivot the table on the "Year" column.

let
    Source = Data,
    // First part from Source:
    #"Removed Columns" = Table.RemoveColumns(Source,{"Year2", "Projection2"}),
    TablePart1 = Table.RenameColumns(#"Removed Columns",{{"Year1", "Year"}, {"Projection1", "Projection"}}),
    // Second part from Source:
    #"Removed Columns1" = Table.RemoveColumns(Source,{"Year1", "Projection1"}),
    TablePart2 = Table.RenameColumns(#"Removed Columns1",{{"Year2", "Year"}, {"Projection2", "Projection"}}),
    // Combine both parts:
    CombinedTableParts = TablePart1&TablePart2,
    // Adjust "Year" column:
    #"Changed Type" = Table.TransformColumnTypes(CombinedTableParts,{{"Year", type text}}),
    #"Added Suffix" = Table.TransformColumns(#"Changed Type", {{"Year", each Text.From(_, "en-US") & " Totals", type text}}),
    // Pivot "Year" column, summing the values in the "Projection" column:
    #"Pivoted Column" = Table.Pivot(#"Added Suffix", List.Distinct(#"Added Suffix"[Year]), "Year", "Projection", List.Sum)
in
    #"Pivoted Column"