How to change the source data connector in Power Query | Excel Off The Grid
Вставка
- Опубліковано 1 лип 2024
- ★ Want to automate Excel? Check out our training academy ★
exceloffthegrid.com/academy
★ Blog post ★
exceloffthegrid.com/change-so...
★ About this video ★
As organizational IT systems evolve, the Power Query solution you built might no longer use the right connector.
Common examples are local Excel workbooks now stored on SharePoint, or SQL databases replacing CSV files.
The good news is we don't need to rebuild our entire solution from scratch. Instead, we just need to make a few changes to the M code at the start of the query.
I know changing your M code can be daunting, but don't worry-it's not as hard as it might sound. I'll show you how in this video.
0:00 Introduction
0:46 Example
1:36 Backup
2:03 Switch point
2:47 Get better at Power Query
3:18 New connector
4:57 Blend queries
6:57 Deploy
7:52 Clean up
8:06 Conclusion
★ Download 30 most useful Excel VBA Macros ebook for FREE ★
exceloffthegrid.com/
★ Where to find Excel Off The Grid ★
Blog: exceloffthegrid.com
Twitter: / exceloffthegrid
#MsExcel
This is a seriously underrated channel.
Thanks - that is very kind of you to say 😁
Thank you for raising these types of scenarios with SharePoint.
You’re welcome Ivan.
I learned new types today. Thank you.
Great!
I usually make the back up in the original query by commenting the code before the switch point at the time I insert the new retrieve-code (derived from the 'new query').
Funny you get to the advanced editor via the view-menu, there is a shortcut in the main menu as well 🙂
If I want the Advanced Editor, I always go View > Advanced Editor. It's just habit now.
Similar for Group By, Use First Row As Headers, Replace Values. I always go to the Transform ribbon, rather than Home.
Good and useful video. I usually make backup by pasting the full code of the query from Advanced Editor to a text file. Sometimes it is safer to keep it outside of Excel if the latter is hanged while editing.
Super as always 👍, thanks Mark
Thanks Kebin. I hope you're well.
Very good, tks man
No problem 👍
Thanks for all the instructive videos you published. It helps us progressing or perfectioning.
There are two topics you should cover:
1) how to access data files located in the same directory than the Excel files (or a subdirectory, whatever, but a relative way to the Excel file itself). How to keep it working when moving the files (Excel file + associated data files) elsewhere with a subtlety when files are moved to a OneDrive folder mapped on the PC file system (access is no longer through C: but https:).
2) in this video, you have said the “next step”: it is interesting to explain than steps are not necessarily ordered (= executed) the way they are listed. It is not infrequent to need to do something like:
A=sthg
B=sthgelse (with no relation to previous step)
C=f(A, B) (referencing the two previous steps, not only the last one)
Without knowing that, you are obliged to cut your request in three parts referencing each others.
Important to know that only the steps required to produce the results are processed.
Thanks for the suggestions. I'll add them to the list, you never know, they might make it to the top of the list one day 😁
Thanks Mark
You're welcome Ismael - I hope you find it useful.
is there a way for you to create a function that does the same thing? and you could have a separate table in Excel where you'd enter the new connector? not sure if you can do that though
I know you can do it to change the source folder or file but going from Excel to sharepoint???
It's not a simple as that, because the connector functions have different outputs. So you can't change one for another that easily.
When the video started, I got the idea to just have an entry point query which does all the conversions and then use that as a source for the "original data" query. I haven't done it this way but it does seem simpler in case the data changes again. Then you'd just build a new input query from the new source and make any necessary changes and use it as the new source. Any reason to not do it that way instead?
Entry points provide different outputs. For example: the entry point for files in a folder provides different output to the entry point for SharePoint list.
So depending on the connector you will still need some additional transformations to get to a Switch Point.
In your example, the end of the query may be the Switch Point. If you use a Raw/Staging/Load folder structure then that is quite likely.