How to combine multiple columns from a table

Question

My issue is the following: I have a table where I have multiple columns that have date and values but represent different things. Here is an example for my headers:

I Customer name I Type of Service I Payment 1 date I Payment 1 amount I Payment 2 date I Payment 2 amount I Payment 3 date I Payment 3 amount I Payment 4 date I Payment 4 amount I

What I want to do is sumifs the table based on multiple criteria. For example:

I Type of Service I Month 1 I Month 2 I Month 3 I Month 4 Service 1
Service 2
Service 3

The thing is that I do not want to write 4 sumifs (in this case, but in fact I have more that 4 sets of date:value columns).

I was thinking of creating a new table where I could put all the columns below each other (in one table with 4 columns - Customer name, Type of Service, Date and Payment) but the table should be dynamically created, meaning that it should be expanded dynamically with the new entries in the original table (i.e. if the original table has 200 entries, this would make the new table with 4x200=800 entries, if the original table has one more record then the new table should have 4x201=804 records).

I also checked the PowerQuery option but could not get my head around it.

So any help on the matter will be highly appreciated.

Thank you.

Answer 1

You can certainly create your four column table using Power Query. However, I suspect you may be able to also generate your final report using PQ, so you could add that to this code, if you wish.

And it will update but would require a "Refresh" to do the updating.

The "Refresh" could be triggered by

• User selecting the Data/Refresh option
• A button on the worksheet which user would have to press.
• A VBA event-triggered macro

In any event, in order to make the query adaptable to different numbers of columns requires more M-Code than can be generated from the UI, a well as a custom function.

The algorithm below depends on the data being in this format:

• Columns 1 and 2 would be Customer | Type of Service
• Remaining columns would alternate between Date | Amount and be Labelled: Payment N Date | Payment N Amount where N is some number

If the real data is not in that format, some changes to the code may be necessary.

To use Power Query:

• Select some cell in your Data Table
• Data => Get&Transform => from Table/Range
• When the PQ Editor opens: Home => Advanced Editor
• Make note of the Table Name in Line 2
• Paste the M Code below in place of what you see
• Change the Table name in line 2 back to what was generated originally.
• Read the comments and explore the Applied Steps to understand the algorithm

To enter the Custom Function, while in the PQ Editord

• Right click in the Queries Pane
• Add New Query from Blank Query
• Paste the custom function code into the Advanced Editor
• rename the Query fnPivotAll

M Code

let

//Change Table name in next line to be the Actual table name in your workbook
    Source = Excel.CurrentWorkbook(){[Name="Table8"]}[Content],

/*set datatypes dynamically with
   first two columns as Text 
   and subsequent columns alternating as Date and Currency*/
textType = List.Transform(List.FirstN(Table.ColumnNames(Source),2), each {_,Text.Type}),
otherType = List.RemoveFirstN(Table.ColumnNames(Source),2),
dateType = List.Transform(
                List.Alternate(otherType,1,1,1), each {_, Date.Type}),
currType = List.Transform(
                List.Alternate(otherType,1,1,0), each {_, Currency.Type}),
colTypes = List.Combine({textType, dateType, currType}),

    typeIt = Table.TransformColumnTypes(Source,colTypes),

//Unpivot all except first two columns
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(typeIt, List.FirstN(Table.ColumnNames(Source),2), "Attribute", "Value"),
    
//Remove "Payment n " from attribute column
    remPmtN = Table.TransformColumns(#"Unpivoted Other Columns",{{"Attribute", each Text.Split(_," "){2}, Text.Type}}),

//Pivot on the Attribute column without aggregation using Custom Function
    pivotAll = fnPivotAll(remPmtN,"Attribute","Value"),
    typeIt2 = Table.TransformColumnTypes(pivotAll,{{"date", Date.Type},{"amount", Currency.Type}})
in
    typeIt2

Custom Function: fnPivotAll

//credit: Cam Wallace  https://www.dingbatdata.com/2018/03/08/non-aggregate-pivot-with-multiple-rows-in-powerquery/

(Source as table,
    ColToPivot as text,
    ColForValues as text)=> 

let
     PivotColNames = List.Buffer(List.Distinct(Table.Column(Source,ColToPivot))),
     #"Pivoted Column" = Table.Pivot(Source, PivotColNames, ColToPivot, ColForValues, each _),
 
    TableFromRecordOfLists = (rec as record, fieldnames as list) =>
    
    let
        PartialRecord = Record.SelectFields(rec,fieldnames),
        RecordToList = Record.ToList(PartialRecord),
        Table = Table.FromColumns(RecordToList,fieldnames)
    in
        Table,
 
    #"Added Custom" = Table.AddColumn(#"Pivoted Column", "Values", each TableFromRecordOfLists(_,PivotColNames)),
    #"Removed Other Columns" = Table.RemoveColumns(#"Added Custom",PivotColNames),
    #"Expanded Values" = Table.ExpandTableColumn(#"Removed Other Columns", "Values", PivotColNames)
in
    #"Expanded Values"

Sample Data

Output

If this does not give you what you require, or if you have issues going further with it to generate your desired reports, post back.