Power Query (Excel) + M Code | Self Reference - Incremental Load
ฝัง
- เผยแพร่เมื่อ 24 ธ.ค. 2024
- This video addressed challenges other experienced:
Incremental Load & List of date with day of the week.
Additional Excel array formulas: SEQUENCE, FILTER, LET, & HSTACK
Topic:
Power Query
0:41 - Incremental Load - Self Referencing
2:35 - List of date with day of the week
Excel
4:39 - SEQUENCE
5:15 - FILTER
5:50 - LET & HSTACK
Power Query Create a Calendar Table with M code:
• Power Query (Excel) + ...
Workbook:
N/A
Happy new year for you and your loved ones. Your solutions are always brilliant. you must have written the excel code to master the App like that! ;) Brilliant indeed.
Thank you Jérome for your compliment. I build model with Power Query more than Excel nowadays. My clients will be terrified if I write excel formula 😅 as they like change or update the formula if they are able to see the formulas, whereas, power query hide all the code in the background.) Let me know if you guys like to see some excel formula examples. Happy 2023!
Loved the self referencing tip :) I use it too.. But your way sounds much better.. brilliant :)
Thank you Sumanth. I'm glad you find it useful. 😁
Merry Christmas & Happy New Year! The self-reference trick is interesting, never thought about that. Thank you for the tip.
Merry Christmas @aatsw. Thank you for your support
This is a good trick, but how to make a reference if I am having my data load from folder > local C drive and not Excel files but like zips. baks and so on? and I just wanna have the status of folders (like date modified, name and so on) if the folder was updated or not.
I just want to read the data info on a daily basis, but create the history that is saved and appending.
Mirrrvelll, zip file can be tricky. Custom connector required to decompress the file using the binary function. I attempted once in the past but beyond my know back then. I know it is possible, might attempt it again sometime in future.
Great one!! Lets just summarize to my understanding, this self-reference trick which I find "wow!", is like, imagining I got a folder with some excel file that is lets say daily/weekly/monthly refreshed with a new file (assuming same structure), and my Query file will just grow ignoring the thing that there is only one newest file - it will keep all the history actually? Am I right?
So I dont need to worry about keeping XYZ of Excel files into one folder just to hold the history... seems very good to me man!
Merry Xmas and alle the best in New Year!
p.s. You improved your video/sound quality so good.
Hi mirrr velll, that's correct. It will accumulate the load as you go. If you accidentally loaded the wrong one, you will then need to manually delete them if the list. Note: better do a backup in case something went wrong.
Thank you. I'm trying to improve the video but a slow progress.