Search code examples
excelvbapowerquery

Excel: Pivoting/Transforming monthyly expense data

I have an excel workbook that has a sheet for every month of the year. each sheet is used to capture some basic general ledger information for that month.

I have managed to create a power query to pull all data for each sheet and combine such that they look like this:

enter image description here

NAME SUPPLIES UTILITIES INSURANCE ADVERTISEMENT PAYROLL MONTH
Entity 1 100 100 100 100 JAN
Entity 2 200 200 200 JAN
Entity 3 50 400 JAN
Entity 1 100 100 100 100 FEB
Entity 2 200 200 200 FEB
Entity 3 50 400 FEB

I would like to use this query now in a new tab but such what the columns are the months, rows are the the columns except 'Name', and values of the sell are summed accordingly, so should end up with something like this:

enter image description here

I have tried to do this by using a pivot table/report, but unsuccessfully. instead of listing the selected columns as individual rows, it is also summing them across..so that i end up with 1 row..but multiple column groupings of 'jan' and feb'. similar to this:

enter image description here

Anyone know if this can be done with a pivot? or do i need to do the pivoting, grouping and aggregation with powerquery much like we would in SQL? if so are there examples of doing this w/ power query?

thanks much

Solution

  • This can also be accomplished using Power Query, available in Windows Excel 2010+ and Excel 365 (Windows or Mac)

    enter image description here

    To use Power Query follow the steps:

    • First convert the source ranges into a table and name it accordingly, for this example I have named it as Table1
    • Next, open a blank query from Data Tab --> Get & Transform Data --> Get Data --> From Other Sources --> Blank Query
    • The above lets the Power Query window opens, now from Home Tab --> Advanced Editor --> And paste the following M-Code by removing whatever you see, and press Done
    let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"NAME", "MONTH"}, "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Other Columns",{"NAME"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[MONTH]), "MONTH", "Value", List.Sum),
    #"Renamed Columns" = Table.RenameColumns(#"Pivoted Column",{{"Attribute", "Categories"}})
in
    #"Renamed Columns"

    enter image description here

    • Lastly, to import it back to Excel --> Click on Close & Load or Close & Load To --> The first one which clicked shall create a New Sheet with the required output while the latter will prompt a window asking you where to place the result.