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.
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.
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.
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???
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.
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.
I learned new types today. Thank you.
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 😁
Super as always 👍, thanks Mark
Thanks Kebin. I hope you're well.
Very good, tks man
No problem 👍
Thanks Mark
You're welcome Ismael - I hope you find it useful.
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.
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.