Sign up for our free Insiders program and get the example file: exceloffthegrid.com/insiders-signup/ File Reference for this video: 0218 Change category - keep history
Mark - This was fantastic! This was the 1st Lambda I’ve seen that I actually understood. I often have a need at work to see how categories have changed over a period of time and my existing solution(s) were either too manual or more fussy that I’d like. I also had no idea you could use a function inside of Replace Values to generate a dynamic replacement value. I can’t wait to try these techniques out with the data I deal with at work. 💪🏾
Great explanation Mark! I just hat a second look at Alberto Ferrari and Marco Russo's book: "Analyzing Data with Microsoft Power BI and Power Pivot for Excel". Chapter 5 is about SCD's but now I understand! thanks!
Hello, Amazed with your presentation in Power Query, just want to get acknowledged why two columns are selected while merging Query, utility of such process
Brilliant presentation, both of the slowly changing dimension problem and of using byrow with lambda to create a spill. I manage operational data for a 220-odd strong corporate function, with active job dates extending back over 10 years and roughly a thousand assignments all told considering promotions, moves, new hires, transfers, retirements, etc. Rather than creating the merged table with a distinct date list for each assignment, I've used DAX formulations to generate a headcount for each date. Now that I understand your approach here it would be simpler, but the merged table which supports the grid calculations would be quite long. Are there any particular cautions in "scaling" your approach to much larger data sets?
Try it, I think you will be surprised how quick it is. Your biggest issues is more likley to be how much data Excel can comfortably handle - 1000 rows and 120 months, is pushing 120,000 rows; so the formulas based on that data is more likely to struggle. If you have a lot of data, then you can use a similar same approach to create an alternative key for creating relationships in the data model.
Hi Mark Sorry to bring this up again, but I see you are also now displaying dates in US format instead of UK format. Is this a change you have made, or has there been a change in Power Query editor to default to US formatted dates all the time? I did a M365 repair plus a Win10 inline reinstall to fix this behavior previously, but now it is in US format every time I bring date data into PQE. Oddly, if I close and load the data reverts to UK format in the workbook. It isn't a deal breaker, but it is extremely frustrating having wrongly formatted dates. Anyone else having the same issue with this? Cheers, Alan
I had the same issue as you for about 2 days; then I got an Office update which appeared to roll back to a previous version of Power Query, and it fixed the issue. So, I think it's definitely a bug, which has now been resolved. Check to see if you have an update to install.
@@ExcelOffTheGrid Thanks Mark. I was beginning to think it was just my setup until I saw your video with US formatted dates. I'll check for updates later today. Cheers. Alan
3:25 I did it this way: = Table.AddColumn(#"Replaced Value", "Date", each { Number.From([Date From]) .. Number.From([Date To])}, type date) Which would be better and why?
![[LIVE] : ONE ลุมพินี 83 วันนี้!! คู่เอก "พันฤทธิ์ vs ซุปเปอร์บอล วันของโอม MBK"](http://i.ytimg.com/vi/1b4pkKHa_Fk/mqdefault.jpg)
Sign up for our free Insiders program and get the example file:
exceloffthegrid.com/insiders-signup/
File Reference for this video: 0218 Change category - keep history
I learned more in 10 minutes than I have in the past 2 months 'playing' with Excel - Subscribed!
I was going to comment the exact same. The applications of these techniques are virtually endless.
Playing is valuable, but it's always helpful to get some outside input to give you some new ideas.
Agreed... the end occurs when the world ceases to have data problems 😁
This is a higher level of learning
Thanks. I was on your level for the beginning, then you took it to new levels with TextJoin, BYROW, and LAMBDA! Well done.
Maybe I will do a video about BYROW/LAMBDA then. As it's a really powerful combination.
Nice combination of wonderfull functions . Thanks a lot
Glad you liked it - I hope you can put it to good use.
Wow, thank you for explaining this so clearly in the right speed. I do not need it now, but I stored this technique for later.
Great stuff. I'm sure it will come in useful at some point.
Blooming heck. Never thought this was possible in excel. Great job and well explained
Almost everything is possible with Excel 😁
Another fantastic video, with a great example!!! that LAMBDA functions looks like its worth exploring more as never dabbled with it
LAMBDA is great for creating very flexible reporting.
Maybe I should do a specific video about BYROW/LAMBDA. I think it might be useful.
@ExcelOffTheGrid yeah definitely, it look quite scary/complicated so never attempted so I would definitely be interested in something like that
@@ExcelOffTheGrid definitely worth it. Would be interesting to explain why some functions won't work with byrow/lambdas too.
Mark - This was fantastic! This was the 1st Lambda I’ve seen that I actually understood. I often have a need at work to see how categories have changed over a period of time and my existing solution(s) were either too manual or more fussy that I’d like. I also had no idea you could use a function inside of Replace Values to generate a dynamic replacement value. I can’t wait to try these techniques out with the data I deal with at work. 💪🏾
Great example I have exactly the same issue and didn’t know this way to solve it. Thank you very much
You are awesome, this was a fascinating technique! Enjoyed watching.
Thank you for your time and consideration in providing this informative tutorial.
Glad it was helpful! 👍
Super video Mark, thank you
Glad you enjoyed it 😁
Great explanation Mark! I just hat a second look at Alberto Ferrari and Marco Russo's book: "Analyzing Data with Microsoft Power BI and Power Pivot for Excel". Chapter 5 is about SCD's but now I understand! thanks!
Hello,
Amazed with your presentation in Power Query, just want to get acknowledged why two columns are selected while merging Query, utility of such process
Awesome 💥💥
Thank you! Cheers! 😁
Brilliant presentation, both of the slowly changing dimension problem and of using byrow with lambda to create a spill.
I manage operational data for a 220-odd strong corporate function, with active job dates extending back over 10 years and roughly a thousand assignments all told considering promotions, moves, new hires, transfers, retirements, etc. Rather than creating the merged table with a distinct date list for each assignment, I've used DAX formulations to generate a headcount for each date. Now that I understand your approach here it would be simpler, but the merged table which supports the grid calculations would be quite long. Are there any particular cautions in "scaling" your approach to much larger data sets?
Try it, I think you will be surprised how quick it is. Your biggest issues is more likley to be how much data Excel can comfortably handle - 1000 rows and 120 months, is pushing 120,000 rows; so the formulas based on that data is more likely to struggle.
If you have a lot of data, then you can use a similar same approach to create an alternative key for creating relationships in the data model.
My tip of the day: MAP() is the same as BYROW() or BYCOL() if only a single column or row is selected respectively.
Yes, that is true.
But would you say that VLOOKUP is the same as INDEX/MATCH if the lookup value is in the first column? 🤔
Does the new ‘Trimrange’ function overcome this (second part) of your challenge as well?
I don't believe so - I don't think TRIMRANGE would help. But it does that would be great.
Hi Mark
Sorry to bring this up again, but I see you are also now displaying dates in US format instead of UK format. Is this a change you have made, or has there been a change in Power Query editor to default to US formatted dates all the time?
I did a M365 repair plus a Win10 inline reinstall to fix this behavior previously, but now it is in US format every time I bring date data into PQE.
Oddly, if I close and load the data reverts to UK format in the workbook. It isn't a deal breaker, but it is extremely frustrating having wrongly formatted dates.
Anyone else having the same issue with this?
Cheers,
Alan
I had the same issue as you for about 2 days; then I got an Office update which appeared to roll back to a previous version of Power Query, and it fixed the issue.
So, I think it's definitely a bug, which has now been resolved. Check to see if you have an update to install.
@@ExcelOffTheGrid Thanks Mark. I was beginning to think it was just my setup until I saw your video with US formatted dates.
I'll check for updates later today.
Cheers.
Alan
3:25
I did it this way:
= Table.AddColumn(#"Replaced Value", "Date", each { Number.From([Date From]) .. Number.From([Date To])}, type date)
Which would be better and why?
Yes, that will work too.
I don’t think it makes much difference which method you use.
Didnt even understand the purpose 😊
Rewatch the intro - it explains the purpose.