How to create a relative file path in Power Query
39.1 هزار بار بازدید -
5 سال پیش
-
Instructions and Download File/Blog Post
Instructions and Download File/Blog Post - https://excel.tv/how-to-create-a-rela...
FREE Dashboards Webinar - https://excel.tv/webinar
FREE Excel Power Users Guide - https://excel.tv/free-power-user-quic...
FREE Data Modeling Webinar - https://events.genndi.com/register/16...
FREE Dashboard Webinar - https://events.genndi.com/channel/exc...
FREE Excel Consulting Tips - https://excel.tv/consultants-corner2/
WEBSITE - http://www.excel.tv
FACEBOOK PAGE - Facebook: ExcelTVSeries
TWITTER - Twitter: excelTV
// Would you ever use this? If so, what would you use it for? Please let us know in the comments.
/// Instructions
1. To use Power Query hack, first connect to another spreadsheet using Power Query. You can do this by going into the Data Tab and selecting Get and Transform, select From File and select the desire Excel file you'd like to load to Power Query.
2. When finished, select Close and Load To.
3. Next, create a new worksheet tab called Setup.
4. In a desired cell add in the following formula = =LEFT(CELL("filename",$A$1),FIND("[",CELL("filename",$A$1),1)-1)
5. Name this cell Filepath
6. Doubleclick on your query and open the Power Query Editor
7. Open the Advanced Editor
8. Above the source, place the following text:
Filepath = Excel.CurrentWorkbook(){[Name="Filepath"]}[Content]{0}[Column1],
9. Change the source to include the M code variable you just created:
Source = Excel.Workbook(File.Contents(Filepath & "Sample Table.xlsx"), null, true),
10. Run it to ensure that it works.
#ExcelTV #MSExcel
FREE Dashboards Webinar - https://excel.tv/webinar
FREE Excel Power Users Guide - https://excel.tv/free-power-user-quic...
FREE Data Modeling Webinar - https://events.genndi.com/register/16...
FREE Dashboard Webinar - https://events.genndi.com/channel/exc...
FREE Excel Consulting Tips - https://excel.tv/consultants-corner2/
WEBSITE - http://www.excel.tv
FACEBOOK PAGE - Facebook: ExcelTVSeries
TWITTER - Twitter: excelTV
// Would you ever use this? If so, what would you use it for? Please let us know in the comments.
/// Instructions
1. To use Power Query hack, first connect to another spreadsheet using Power Query. You can do this by going into the Data Tab and selecting Get and Transform, select From File and select the desire Excel file you'd like to load to Power Query.
2. When finished, select Close and Load To.
3. Next, create a new worksheet tab called Setup.
4. In a desired cell add in the following formula = =LEFT(CELL("filename",$A$1),FIND("[",CELL("filename",$A$1),1)-1)
5. Name this cell Filepath
6. Doubleclick on your query and open the Power Query Editor
7. Open the Advanced Editor
8. Above the source, place the following text:
Filepath = Excel.CurrentWorkbook(){[Name="Filepath"]}[Content]{0}[Column1],
9. Change the source to include the M code variable you just created:
Source = Excel.Workbook(File.Contents(Filepath & "Sample Table.xlsx"), null, true),
10. Run it to ensure that it works.
#ExcelTV #MSExcel
5 سال پیش
در تاریخ 1398/05/15 منتشر شده
است.
39,157
بـار بازدید شده