Change category while keeping history | Slowing changing dimensions with Excel and Power Query.

[@ExcelOffTheGrid]

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

[@ttilta]

I love your videos, you handle problems I've actually encountered with such elegance.

[@billsterUK]

I learned more in 10 minutes than I have in the past 2 months 'playing' with Excel - Subscribed!

[@56Nine]

I was going to comment the exact same. The applications of these techniques are virtually endless.

[@ExcelOffTheGrid]

Playing is valuable, but it's always helpful to get some outside input to give you some new ideas.

[@ExcelOffTheGrid]

Agreed... the end occurs when the world ceases to have data problems 😁

[@ryanbartlett672]

Thanks. I was on your level for the beginning, then you took it to new levels with TextJoin, BYROW, and LAMBDA! Well done.

[@ExcelOffTheGrid]

Maybe I will do a video about BYROW/LAMBDA then. As it's a really powerful combination.

[@RichardJones73]

Blooming heck. Never thought this was possible in excel. Great job and well explained

[@ExcelOffTheGrid]

Almost everything is possible with Excel 😁

[@v2pumo817]

Nice combination of wonderfull functions . Thanks a lot

[@ExcelOffTheGrid]

Glad you liked it - I hope you can put it to good use.

[@bryancamareno2831]

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. 💪🏾

[@Bhavik_Khatri]

Thank you for your time and consideration in providing this informative tutorial.

[@ExcelOffTheGrid]

Glad it was helpful! 👍

[@joukenienhuis6888]

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.

[@ExcelOffTheGrid]

Great stuff. I'm sure it will come in useful at some point.

[@Kuriacs]

You are awesome, this was a fascinating technique! Enjoyed watching.

[@90hsilva]

Great example I have exactly the same issue and didn’t know this way to solve it. Thank you very much

[@DinoDelight]

Another fantastic video, with a great example!!! that LAMBDA functions looks like its worth exploring more as never dabbled with it

[@ExcelOffTheGrid]

LAMBDA is great for creating very flexible reporting. Maybe I should do a specific video about BYROW/LAMBDA. I think it might be useful.

[@DinoDelight]

@ExcelOffTheGrid yeah definitely, it look quite scary/complicated so never attempted so I would definitely be interested in something like that

[@michaels4929]

​@@ExcelOffTheGrid definitely worth it. Would be interesting to explain why some functions won't work with byrow/lambdas too.

[@chrism9037]

Super video Mark, thank you

[@ExcelOffTheGrid]

Glad you enjoyed it 😁

[@barttitulaerexcelbart9400]

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!

[@delemtube]

This is a higher level of learning

[@spen2431]

Awesome 💥💥

[@ExcelOffTheGrid]

Thank you! Cheers! 😁

[@SURYADEEPRGOMES]

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

[@houstonsam6163]

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?

[@ExcelOffTheGrid]

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.

[@ironicdaemon]

My tip of the day: MAP() is the same as BYROW() or BYCOL() if only a single column or row is selected respectively.

[@ExcelOffTheGrid]

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? 🤔

[@Paladin101]

Does the new ‘Trimrange’ function overcome this (second part) of your challenge as well?

[@ExcelOffTheGrid]

I don't believe so - I don't think TRIMRANGE would help. But it does that would be great.

[@ExcelWithChris]

Thanks for this one. But if you have for example 1000 products this lookup table will have more than 300 000 within a year? Isnt there a way where you can say if the date in transaction is A then use the lookup value between the before and after columns?

[@ExcelOffTheGrid]

Power Query does not have a data limit. The limit is when the final values hit Excel. So in your example, you won’t get that many records in the output. Therefore, the issue is the speed of Power Query. If you try it out you will be surprised how quickly it handles these transformations. If you are dealing with a lot of data then loading to Power Pivot remains the best options.

[@ExcelWithChris]

@@ExcelOffTheGrid Thanks for this. The system I am looking at will be an Excel file where users capture basic sales data. They will input the number of items and then the file needs to do a basic vlookup/xlookup to return the cost. Now the issue is this cost value can change over time, and therefore the lookup table needs to be inside the file itself. And that is my issue with the table being to "BIG" in rows - even if the lookup table is generated using Power Query (it needs to be a table inside the file). It still needs to be inside the file to have "live" lookup as you type the numbers sold.

[@mrbartuss1]

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?

[@ExcelOffTheGrid]

Yes, that will work too. I don’t think it makes much difference which method you use.

[@alanmonaghan9194]

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

[@ExcelOffTheGrid]

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.

[@alanmonaghan9194]

@@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

[@stanTrX]

Didnt even understand the purpose 😊

[@ExcelOffTheGrid]

Rewatch the intro - it explains the purpose.