Power Query - Sum Last N Columns
HTML-код
- Опубликовано: 9 фев 2025
- Learn how to dynamically discover and aggregate (ex., SUM) the last N columns of a table regardless of column name. Also, see how to filter and assign data types by column position rather than column name. Many cool tricks are involved in the solution.
File Download Link:
www.bcti.com//...
01:21 Solving the Problems Statically
02:05 Understanding the Limits of a Static Solution
03:32 Removing Premature Steps
03:44 Removing Unwanted Columns
06:23 Testing for Dynamic Column Selection
06:49 Summing the Last 3 Columns
07:06 Setting Data Types by Position
10:01 Summing the Last 3 Columns
13:09 Testing the Fully Dynamic Solution
13:33 Overview of the M Code
14:21 Be Mindful When Working With Excel Tables
Interesting video (and solution). I really appreciate that you don't edit mistakes out of the video prior to publication. It helps enormously being aware of problems and their solutions in real time.
Thank you. I thought about redoing that step so as to not include the oversight, but I left it in for just that reason. I'm glad you noticed and found it helpful. Cheers.
@@bcti-bcti I second @ianl1052 that you showing mistakes is almost as valuable as providing the solution.
Great tips - many thanks! I've used similar techniques where the number of columns I need to sum is not always fixed - it changes dynamically with each row, based on a parameter in another column on the row. You get the number of columns to sum by removing the initial "F" from the front end of the parameter and converting the remaining text to a number and subtracting that number from 13. E.g. if you have F03, you want to sum the rightmost 10 columns (F03 -> 03 -> 3 -> 13-3 = 10, so sum the rightmost 10 columns). Here, the F03 parameter is a forecasting reference that denotes a forecast for periods (months) 3 to 12 (i.e. periods 1 and 2 have Actuals).
That's a cool idea; letting the user select the number of months to calculate from something like a Data Validation dropdown list, then feeding that value into the query as a parameter. I'm going to try that. Thanks for the idea!!!
As always, absolutely professional. A pleasure to watch your videos. Thank you very much, Bryon!
Thank you! Be sure to check out the recommended optimizations int he comments. Some are really good; much better than my solution.
Only thing I would do differently is change the Changed Type step to:
Table.TransformColumns(
Promote_Header_Row,
{"Name", Text.From},
Currency.From
)
I got this tip literally before watching this video from Alejandra Horvath - Dynamically Transform Data Type for All Columns with Table.TransformColumns. Although, I personally would've used my custom function that dynamically sets the data types of columns without hardcoding any column names.
Same source for me but some corrections to apply on your formula i think
That solution ROCKS!!! I wish I had seen that video sooner; my solution would have definitely used it instead of what I did. I can see my solution for other use cases, though. But for this case, her solution is much better. Thanks for sharing.
awe-inspiring, good job really 👍
@@nicor1501 Wow! I had no idea I would inspire awe. Thanks
Brilliant solution, thanks Bryon!
Thanks. Be sure to read the comments. Many viewers are posing some FANTASTIC tips for making this even better. Thanks for watching.
Amazing... and very useful! Thanks 👌
Thanks. Make sure to read the comments. Some other viewers have contributed some great optimization techniques that are better than the ones I showed in the video.
Brilliant as always 👍❤
Thank you! Cheers!
Extraordinario video. Thanks very much.
You're very welcome. Thanks for watching.
Amazing 👏
@@anindyamukherjee7690 Thanks!
Thanks for this. I learned the record.removefields and will try it on my next projects
Cool! I hope it helps make life simpler. Thanks for watching.
In one of the projects I am working on , with CSV files that are placed SharePoint folder, It doesn't work if I didn't specify the number of columns.
That's good to know. I hadn't tested it when pulling data from SharePoint files; I just assumed it would work the same. Good to know. Thanks for letting us all know.
Great as usual! Instead of fighting the change type feature, have you considered Number.From? sometimes it is more straightforward even though the column is not officially declared as number.
That was mentioned in an earlier comment and I think it's a GREAT idea. I wish I had known about that trick before posting this video. My solution would have been much more elegant. Thanks for watching.
I've a question unrelated to this video, i have a dataset with about 10,000 rows of data each having a primary key which i use to merge with another dataset having the same number (a little less actually) of keys. When i merge the queries they merge alright within a minute, but when i expand the columns it takes forever and just never loads up. Any idea why? If i instead join those tables using the lookupvalue in data model it works fine.
First thing I check is if I can remove unnecessary columns in both tables before merging to improve performance (refer to Chris Webb's: Optimising The Performance Of Power Query Merges In Power BI, Part 1: Removing Columns).
I would also check your steps in your 2nd table that you want to merge with to see if it is unoptimised. Table.Buffer is a last resort, although I'm still unsure on when is the proper time to use it.
I had this problem once in Power BI. My solution was not to merge the two tables in Power Query, but to connect the tables in the data model view like a lookup table. Performance got super fast.
@@benndii Best practice is to always do operations in Power Query unless the operation can only be done in DAX. Power Query will be more efficient than DAX during refreshes.
If you're loading the 2nd table into the report just to be used as a lookup for the 1st table, that is also bad practice for increasing file size unnecessarily.
@@swankykoala4836 Well sorry to break the rules of the universe by ignoring we'll known best practises. All I wanted to say was that in my special case that was the best solution.
That's an interesting problem. Would you be able to send me a sample file with your current solution(s) so I can do some investigating and testing? Thanks. (training@bcti.com)
ruclips.net/video/kh681biUveI/видео.html For the Excel workbooks, if there will only ever be one worksheet containing the required data, can remove the Navigation step and update the Source step to be
Excel.Workbook(File.Contents("C:\LastN\Test Data - Many.xlsx"), null, true){0}[Data]
That's FANTASTIC!!! What a great tip. I'm going to add that to my list of tricks. I wish I had known that earlier so I could have included it in the video. Oh, well. You can't learn everything in a day. Thanks again.
@@bcti-bcti
Slightly off-topic, but...
The same can be done when using the `Folder.Contents` or `Sharepoint.Contents` when navigating down a folder / document library structure and a list of files is needed. Say a list of files in a folder named "C:\Users\username\Documents\Sales Data\CSV Files\" is needed
From this -
let
Source = Folder.Contents("C:\Users\username\Documents"),
#"Sales Data" = Source{[Name="Sales Data"]}[Content],
#"CSV Files" = #"Sales Data"{[Name="CSV Files"]}[Content]
in
#"CSV Files"
To this -
let
Source = Folder.Contents("C:\Users\username\Documents"){[Name="Sales Data"]}[Content]{[Name="CSV Files"]}[Content]
in
Source
Some manual work to amend the M code, but not a biggie.
I think you can improve the data type change this way (but not in Currency.Type).
Table.TransformColumns(Promote_Header_Row,{{"Name",type text}},Number.From)
It works with Currency.From...
Table.TransformColumns(Promote_Header_Row,{{"Name",type text}},Currency.From)
That solution ROCKS!!! I wish I had seen that video sooner; my solution would have definitely used it instead of what I did. I can see my solution for other use cases, though. But for this case, her solution is much better. Thanks for sharing.
1
I assume "1" means "This is the most amazing RUclips video I have ever watched!!!" 😄
Interesting, as always! Some possibilities I see to improve the code:
- there is a frequently recurring reference to the names of the columns, I could help to assign this list to a variable and the use the variable afterwards. For example, lastColumns = Table.ColumnNames(....) and then later lastColumns{1} etc
- I always try to change the filetype when I add a new column, this saves a step (and I see you have done this in the final version)
- I haven't tried this, but maybe you can use List.Zip to create a list of lists to set the type for the last 3 columns. For example List.Zip({lastColumns, List.Repeat({Currency.Type}, 3)})
- Lastly, this works well if the columns are properly sorted months, or if you just want the last three columns, but if you want to be sure you capture the last 3 months you can probably do some magic with Unpivot other columns, turn the column names into dates, select the latest 3 dates and then use the matching column names as the lastColumns list. More dynamic, but also slower I guess?
It has been suggested by other viewer a super cool way to set the data types. Check out what they suggest; it's a bit easier than creating the list of lists. I agree 100% that if the data is not provided in chronological order, some additional steps to ensure proper sorting by date would be needed. Good to see you thinking proactively. Thanks for the suggestion and taking the time to watch.
ruclips.net/video/kh681biUveI/видео.htmlsi=sNP8zcF085vG2M-f&t=590
my solution for dynamic conditionally changing the columntypes would be like this
Set_Data_Types = Table.TransformColumnTypes(Promote_Header_Row,List.Transform(Table.ColumnNames(Promote_Header_Row),each if _ = "Name" then {_, type text} else {_, Currency.Type}))
I think this technique is FAR superior to mine. I've changed my ways and am now doing it this way. Thanks for sharing.
@@bcti-bcti I am happy if I could make a meaningful contribution. the code creates a list based on the collumnnames. Each List contains the Columnname as first element, the datatype a second element. this list-of-lists feeds the Changetype-step.
ruclips.net/video/kh681biUveI/видео.htmlsi=Lb8_XqckVXGkDbcR&t=934 remove the changetype step completly and change the sum_last_3-step into this and you are good to go.
Sum_Last_3 = Table.AddColumn(Promote_Header_Row, "Total", each List.Sum( List.Transform(Record.ToList(Record.RemoveFields(_, {"Name"} ) ) ,each Currency.From(_,"en-US"))))
I'm a BIG fan of combining smaller steps into a single, large step...
...but I can also see the advantage of keeping more sophisticated steps separate so as to make the code more easily understood. Especially when it's ME that is looking at my own code a year later, trying to figure out what the heck I was thinking. Thanks for watching and contributing.