I would use vba and power query. PQ is master in manupulating/transform data. Done a similar project. Totally automate not necessary. 15 minutes labor work.
Hello! Thanks for the comment. For context, this is just an example using a small dataset. Originally, this was a VBA and Power BI project where data manipulation was handled with Power Query in Power BI. However, I encountered a bottleneck when I needed a database solution to manage over 30 tables feeding into Power BI, which impacted refresh performance if all the data are stored in Excel. That’s why I switched to Python. It interacts with databases like SQLite much more efficiently. The ability to use SQL to manipulate data before pushing it into Power BI is an added benefit, but the main goal is still to manage all the tables, which was challenging to do with VBA alone. For smaller projects, I agree that VBA and Power Query are more than sufficient and using a database solution would simply be an overkill.
Hello! Thanks for your kind words. I always start with a clear end goal in mind and then work backwards, breaking it down into manageable steps. To fill in the knowledge gaps along the way, I rely on a few trusted resources. Google is my go-to for researching new concepts and finding relevant information. The Python community is incredibly vast and supportive, with many valuable resources available on platforms like Reddit and Stack Overflow. With the development of AI, if there's any concepts that I don't really understand, I would ask ChatGPT or META AI to explain it to me with examples. Hope this is helpful to your own learning journey.
Hi Joel . Thank you for this informative video. You are doing a great job. I have request for you to create this project in a tutorial form which covers very details. It will be really helpful.
Hello! Thanks for your kind words. If this video sparks sufficient interest, I'll be happy to create a step-by-step tutorial to guide through building this project from scratch. Please share this with whoever you think would be interested in this project. Thank you!
Hi Joel - if possible can you make video using SAP, VBA, SQL, Power Automate (Cloud) not desktop and Power BI as these are only tools I am allowed to use in my work.
Hello! I'm committed to exploring various tools to cater to diverse user needs. If you have SAP and SQL in your toolkit, I highly recommend seeking IT's approval to install Python. Its vast community and extensive libraries make it an ideal choice for streamlining data transfer between SAP, databases, and Excel. While similar tasks can be performed in VBA, they often require more complexity. Power Automate (Cloud) is better suited for automating data transfers between online files or information. In this example, I did use Power Automate (Cloud) to help me with Power BI refreshes. I'd love to hear from you - what specific workflows do you think would be beneficial for your needs?
Hello! Assuming you are running the script without any user input and your pc is on all the time, you can try using the apscheduler module. You can define the scheduled script to run every 4 hours. Something like the following: from apscheduler.schedulers.blocking import BlockingScheduler sched = BlockingScheduler() @sched.scheduled_job('interval', hours=4) def timed_job(): print('Script within timed_job will run every 4 hours.') sched.start()
Thank you for your support! Currently, I don't have plans to launch an online course, as it requires significant time and effort. I'm also mindful of ensuring there's sufficient demand before investing in such a project. However, if there's enough interest from all of you, I'll definitely consider it. In the meantime, I'm more than happy to help answer any questions you may have. Feel free to book a complimentary call with me, and we can have a chat to see how I can assist you. I'm always here to help! 😊
Hi! You can book a call with me using the link: joelting.com/book-a-call/ During this call, we'll discuss your data management challenges and opportunities, and I'll share my view on how you could optimize your workflows. If you find our conversation valuable and would like to continue with personalized coaching or collaborate on a project, we can discuss further arrangements. Otherwise, consider this call a free consultation - no strings attached!
If you're interested, you can always learn it at your own pace. 😊 Personally, I began with VBA, but soon encountered limitations when trying to move data into a database. This led me to explore Python, and eventually, I was able to develop this end-to-end project that automatically retrieve data and refreshes Power BI. It has been a fun and rewarding journey.
Hello! You can get the code, database and Power BI file by signing up to my email newsletter. joelting.com/newsletter-download Then it would be delivered straight to your email inbox.
Good day Joel, I tried to sign in the email newsletter, but I do not get the confirmation after providing my email. I am very interested to have the code to be able to finish my project. Thanks!!
@@danielruedag1858 Hello Daniel! Sorry for the technical difficulties. It may be in your spam folder. I see you have reached out to me via LinkedIn. I'll send the link over to you.
Only up!!
so much appreciated, i was succesfully running my program based follow on your previous video, keep innovating & sharing mr joel
I'm glad my videos are able to help!
I would use vba and power query.
PQ is master in manupulating/transform data.
Done a similar project.
Totally automate not necessary.
15 minutes labor work.
Hello! Thanks for the comment. For context, this is just an example using a small dataset. Originally, this was a VBA and Power BI project where data manipulation was handled with Power Query in Power BI. However, I encountered a bottleneck when I needed a database solution to manage over 30 tables feeding into Power BI, which impacted refresh performance if all the data are stored in Excel. That’s why I switched to Python. It interacts with databases like SQLite much more efficiently. The ability to use SQL to manipulate data before pushing it into Power BI is an added benefit, but the main goal is still to manage all the tables, which was challenging to do with VBA alone.
For smaller projects, I agree that VBA and Power Query are more than sufficient and using a database solution would simply be an overkill.
Your help is very much appreciated 👏
Hi Joel, I really appreciate your work. You are doing a great job. Where do you get your inspirations from and how do you teach them to yourself?
Hello! Thanks for your kind words. I always start with a clear end goal in mind and then work backwards, breaking it down into manageable steps. To fill in the knowledge gaps along the way, I rely on a few trusted resources. Google is my go-to for researching new concepts and finding relevant information. The Python community is incredibly vast and supportive, with many valuable resources available on platforms like Reddit and Stack Overflow.
With the development of AI, if there's any concepts that I don't really understand, I would ask ChatGPT or META AI to explain it to me with examples. Hope this is helpful to your own learning journey.
Hi Joel . Thank you for this informative video. You are doing a great job. I have request for you to create this project in a tutorial form which covers very details. It will be really helpful.
Hello! Thanks for your kind words. If this video sparks sufficient interest, I'll be happy to create a step-by-step tutorial to guide through building this project from scratch. Please share this with whoever you think would be interested in this project. Thank you!
@@JoelTing Please do it ;)
Hi Joel - if possible can you make video using SAP, VBA, SQL, Power Automate (Cloud) not desktop and Power BI as these are only tools I am allowed to use in my work.
Hello! I'm committed to exploring various tools to cater to diverse user needs. If you have SAP and SQL in your toolkit, I highly recommend seeking IT's approval to install Python. Its vast community and extensive libraries make it an ideal choice for streamlining data transfer between SAP, databases, and Excel. While similar tasks can be performed in VBA, they often require more complexity.
Power Automate (Cloud) is better suited for automating data transfers between online files or information. In this example, I did use Power Automate (Cloud) to help me with Power BI refreshes.
I'd love to hear from you - what specific workflows do you think would be beneficial for your needs?
what if i want multiple tables appended/increment load on the db every 4 hours?? then what should i do?
Hello! Assuming you are running the script without any user input and your pc is on all the time, you can try using the apscheduler module. You can define the scheduled script to run every 4 hours. Something like the following:
from apscheduler.schedulers.blocking import BlockingScheduler
sched = BlockingScheduler()
@sched.scheduled_job('interval', hours=4)
def timed_job():
print('Script within timed_job will run every 4 hours.')
sched.start()
@@JoelTing thank you so much
Buy your ebook, will you take an online course on process automation someday? SAP, PYTHON,.. ?"
Thank you for your support! Currently, I don't have plans to launch an online course, as it requires significant time and effort. I'm also mindful of ensuring there's sufficient demand before investing in such a project. However, if there's enough interest from all of you, I'll definitely consider it.
In the meantime, I'm more than happy to help answer any questions you may have. Feel free to book a complimentary call with me, and we can have a chat to see how I can assist you. I'm always here to help! 😊
Hi Joel, I just noticed you have "book a call with Joel". May I know how much do you charge for the session? Thanks.
Hi! You can book a call with me using the link:
joelting.com/book-a-call/
During this call, we'll discuss your data management challenges and opportunities, and I'll share my view on how you could optimize your workflows. If you find our conversation valuable and would like to continue with personalized coaching or collaborate on a project, we can discuss further arrangements. Otherwise, consider this call a free consultation - no strings attached!
@@JoelTing Too bad I don't know Phyton.😞
If you're interested, you can always learn it at your own pace. 😊 Personally, I began with VBA, but soon encountered limitations when trying to move data into a database. This led me to explore Python, and eventually, I was able to develop this end-to-end project that automatically retrieve data and refreshes Power BI. It has been a fun and rewarding journey.
@@JoelTing that's true. I learned SQL and VBA on my own. I'm not an expert, just enough knowledge to get the job done.
@@yuriitiunov8803 I might give it a shot. Thanks!
is there a way to get this code ,like from github
Hello! You can get the code, database and Power BI file by signing up to my email newsletter.
joelting.com/newsletter-download
Then it would be delivered straight to your email inbox.
Good day Joel, I tried to sign in the email newsletter, but I do not get the confirmation after providing my email. I am very interested to have the code to be able to finish my project.
Thanks!!
@@danielruedag1858 Hello Daniel! Sorry for the technical difficulties. It may be in your spam folder. I see you have reached out to me via LinkedIn. I'll send the link over to you.