Sum of multiple columns based on column names in Power Query

[Merleau]

Hello,

I have a table with multiple columns (more than 30) in no particular order. I need to compute the sum of some of the columns based on the two last characters in the column name.

As an example:

Id	Other_Columns	R1PM	R2AM	R2PM	R1AM	RQPM	CountAM	CountPM	CountAM_PM
id1		1	0	0	0	1	0	2	2
id2		0	0	1	1	0	1	1	2
id3		1	1	1	1	0	2	2	4
id4		0	0	0	1	0	1	0	1

I need to compute the sum of columns ending with  "AM", also the sum of columns ending with "PM" and finally the sum of columns ending with "AM" or "PM".  Depending on the feed, the column names might change but the new ones will still be ending with "AM" or "PM". Hence I need to automate.

Thanks so much for your help.

You guys at community.powerbi.com are the best.

[ziying35]

@Merleau 

To summarize, my solution is to create columns in batches using "List.Accumulate" based on the criteria.

fnAddCols:

In "Table.AddColumn", use "Record.ToList" to convert each Record into a List, and then zip the field name of the Table into the above List.  Next, use "List.Select" to Select the field ending in AM or PM in the zipped List, and sum the selected values in the List. Finally, if the field name is "Count AM_PM", sum the values of the last two fields(Count AM/Count PM) directly

I hope these machine-translated texts will help you understand my general idea. If my solution has solved your problem, please mark it as a solution

[ziying35 · Accepted solution]

// AM_PM
let
    Source = Table.FromRecords(
                Json.Document(
                    Binary.Decompress(
                        Binary.FromText("i65W8kxRslLKTDFU0lHyL8lILYp3zs8pzc0rVrLKK83J0VEKMgzwVbIyBDKMHIEM
                                        AxAjAMowhIkEgtXU6sCNM8JvnAGGcYYw4wxhxhkgGWdMpOsMiTPOhFTXGWAxLhYA",
                              BinaryEncoding.Base64
                        ),
                        Compression.Deflate
                    )
                )
              ),
    chType = Table.TransformColumnTypes(Source,{{"Id", type text}, {"Other_Columns", type any}, {"R1PM", Int64.Type}, {"R2AM", Int64.Type}, {"R2PM", Int64.Type}, {"R1AM", Int64.Type}, {"RQPM", Int64.Type}}),
    fdnames = Table.ColumnNames(chType),
    fnAddCols = (tbl,newfd)=> 
        Table.AddColumn(
              tbl,
              newfd,
              (rec)=> let listvalues=Record.ToList(rec),
                          zip=List.Zip({fdnames, listvalues}) 
                      in if newfd="CountAM_PM" then List.Sum(List.LastN(listvalues,2)) 
                         else List.Sum(List.Transform(List.Select(zip, each Text.EndsWith(_{0},Text.End(newfd, 2))), (x)=>x{1})) 
        ),
    result = List.Accumulate({"CountAM", "CountPM", "CountAM_PM"},chType,(s,c)=>fnAddCols(s,c))
in
    result

If this is what you want to achieve, mark it as the solution

[Merleau]

Thank you @ziying35

Your appraoch looks like a solution to my problem.

I am replicating this with real data.

A little bit of comments will be welcome to better understand the methodology.

Thanks so much for your input.

[mahoneypat]

This data table should be unpivoted to do the calculation easily.  You could also consider splitting the unpivoted attribute column to have an AM/PM column to make it even easier.  Does that work for your model?

Regards,

Pat

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[Merleau]

Hi Pat

Thank you for the prompt response. I know the solution lies in unpivoting the columns. But I am struggling with the steps to get to the actual sums for only those selected columns.

Can you pls provide a more detailed procedure?

I sincerely appreciate the help.

Thank you

[mahoneypat]

Try putting this M code in a blank query.

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WykwxVNJRUgBiEG2AhGF8IzCO1QGpNYKqRVZjiMaGqTVGMtcQTa0RFJtA1ZqgmWuAZi6Ejo0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Id = _t, Other_Columns = _t, R1PM = _t, R2AM = _t, R2PM = _t, R1AM = _t, RQPM = _t, CountAM = _t, CountPM = _t, CountAM_PM = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Id", type text}, {"Other_Columns", type text}, {"R1PM", Int64.Type}, {"R2AM", Int64.Type}, {"R2PM", Int64.Type}, {"R1AM", Int64.Type}, {"RQPM", Int64.Type}, {"CountAM", Int64.Type}, {"CountPM", Int64.Type}, {"CountAM_PM", Int64.Type}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"CountAM", "CountPM", "CountAM_PM"}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Removed Columns", {"Id", "Other_Columns"}, "Attribute", "Value"),
#"Split Column by Position" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByPositions({0, 2}, false), {"Attribute.1", "Attribute.2"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Position",{{"Attribute.1", type text}, {"Attribute.2", type text}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"Attribute.2", "AM or PM"}, {"Attribute.1", "R Type"}})
in
#"Renamed Columns"

Then close apply and write this measure - 

SumValue = SUM(AMPMTable[Value])

Then make a matrix with id as rows and "AM or PM"  and "R Type" columns in a matrix (with drilled down) to get this visual.

Is this what you were looking for?

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[Merleau]

Thank you Pat for the help.
I appreciate the time you put in creating this query.
However, because I am still learning M language and for reasons mentionned below, this will be difficult to apply to solve my problem:

1- I need the solution in Power Query because those columns CountAM, CountPM, CountAll
are used in subsequent steps for more analysis
2- To display the final result, I have to use a table not a matrix because I need to reorder columns, edit names ...
3- Also column names are completely arbitrary, different lenght, alpha numeric characters, space... The only consistency is they all end with "AM" or "PM".

I also compute more measures on the final table in my report.
I am reading resource on M language to understand it better. Any pointers are welcome.
Thank you.

[mahoneypat]

Saw your reply.  Below is a response to each of your points:

1. I would still do all your calculation in DAX instead of M/Query.  You can write base measures for the different counts and then use those in more advanced calculations

2. The use of query of not should not drive you to use a Table vs Matrix.  If you need columns for axes, legends, etc., you can always write DAX calculated columns.

3. I think I splite the columns at 2 characters, but you could easily replace that with a query that just gets the last two characters (indepent of the overall string length).

If you really want to dig into M, I recommend "M is for Data Monkey" (check your local online library); however, I would encourage you to learn DAX at the same time, so you don't try to do things in M that should be done in DAX.

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[Merleau]

Thank you @Pat

I agree with learning DAX and M at the same time.

For this report, requirements are very tight and the end user needs some flexibility for simple and easy edit.