How to make paths dynamic so that Power Query connections work on Dropbox shared files
23 هزار بار بازدید -
6 سال پیش
-
Previous videos: 1st Query -
Previous videos:
1st Query - Selecting all Columns of a spreadsheet with Power Query does not get dynamic - Selecting all Columns of a spreadshee...
2nd Query - Get the data from the most recent file in a folder with Power Query -
Get the data from the most recent fil...
Case Scenario: You have a file on a Dropbox folder that contains Power Query connections, and you need those connections to work regardless of the computer from which you open that file.
Solution: Replace the path string in those queries by another string produced by another query that reads the location of the file when you open it.
This video explains how you can:
- Use the CELL formula to indicate file and folder paths
- Create a query in Power Query that produces a text string
- Replace the path string in the M code of a query
- Change the privacy settings in Power Query to allow file and folder paths defined by queries
@Oakley Turvey also posted a solution to this problem. Please see his video at PowerQueryDynamicFolderPath
__________________
Learn how to automate your Excel Reports in minutes instead of hours without copy-paste or coding: https://snapreportschamp.com/course
________________
Other techniques:
By Ken Puls - https://www.excelguru.ca/blog/2015/03...
By @ExcelTurbo - Excel Turbo - Como extrair arquivos d...
Caution when disabling Query Data Privacy checks, especially if you are using Power Query in Power BI - read this blog post by Chris Web, especially the last two paragraphs and the other post he mentions there: https://blog.crossjoin.co.uk/2018/12/...
Discussion on Microsoft's uservoice platform about the firewall issue: https://excel.uservoice.com/forums/30...
Playlist “Excel and Power Query” – Excel and Power Query
Subscribe to the channel - @celiaalvessolveexcel
Intro video clip by Daniela Alfarrobinha – https://vimeo.com/danielaalfarrobinha
1st Query - Selecting all Columns of a spreadsheet with Power Query does not get dynamic - Selecting all Columns of a spreadshee...
2nd Query - Get the data from the most recent file in a folder with Power Query -
Get the data from the most recent fil...
Case Scenario: You have a file on a Dropbox folder that contains Power Query connections, and you need those connections to work regardless of the computer from which you open that file.
Solution: Replace the path string in those queries by another string produced by another query that reads the location of the file when you open it.
This video explains how you can:
- Use the CELL formula to indicate file and folder paths
- Create a query in Power Query that produces a text string
- Replace the path string in the M code of a query
- Change the privacy settings in Power Query to allow file and folder paths defined by queries
@Oakley Turvey also posted a solution to this problem. Please see his video at PowerQueryDynamicFolderPath
__________________
Learn how to automate your Excel Reports in minutes instead of hours without copy-paste or coding: https://snapreportschamp.com/course
________________
Other techniques:
By Ken Puls - https://www.excelguru.ca/blog/2015/03...
By @ExcelTurbo - Excel Turbo - Como extrair arquivos d...
Caution when disabling Query Data Privacy checks, especially if you are using Power Query in Power BI - read this blog post by Chris Web, especially the last two paragraphs and the other post he mentions there: https://blog.crossjoin.co.uk/2018/12/...
Discussion on Microsoft's uservoice platform about the firewall issue: https://excel.uservoice.com/forums/30...
Playlist “Excel and Power Query” – Excel and Power Query
Subscribe to the channel - @celiaalvessolveexcel
Intro video clip by Daniela Alfarrobinha – https://vimeo.com/danielaalfarrobinha
6 سال پیش
در تاریخ 1397/06/30 منتشر شده
است.
23,084
بـار بازدید شده