Forum Discussion
Helper IISum of multiple columns based on column names in Power Query
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.
// 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 resultIf this is what you want to achieve, mark it as the solution
9 Replies
- ziying35
Impactful Individual
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
- mahoneypat
Microsoft Employee
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
MerleauHi 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
- ziying35
// 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 resultIf this is what you want to achieve, mark it as the solution
