Search code examples
excelpowerquerym

Power Query - Dynamically Merge and Sum Columns

After some transformations, i get a following table, where each column = week number with some value. Due to week split between months, I get some columns which I need to sum.

In example bellow - Column "48" and "481" should be summed and named "48".

enter image description here

Is it possible to dynamically sum columns based on first two numbers ? (XX = sum of XX & XX1)

Solution

  • Add an index. enter image description here

    Select index column and unpivot other columns enter image description here

    Select attribute column and extract first two chars. enter image description here

    enter image description here

    Group and sum.

    enter image description here

    Pivot back.

    enter image description here

    enter image description here