Forum Discussion

cristianml's avatar
cristianml
Post Prodigy
6 years ago
Solved

Change Source = Excel.CurrentWorkbook() name sheet

Hi,   I need help to change in advance editor in power query the source so it can get the data from an Excel tab called "FT Actual" that is in the current workbook. (.NOT FROM TABLE).   In someho...
General Comment
Interesting Links
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • KNP's avatar
    KNP
    6 years ago

    Hi cristianml ,

     

    You'll need something like this to get the sheet names and then navigate to the specific sheet you need.

    From what I understand you can't acheive this using Excel.CurrentWorkbook().

     

    let
    Source = Excel.Workbook(File.Contents("YourFilePath"), null, true),
    #"Filtered Rows" = Table.SelectRows(Source, each ([Kind] = "Sheet")),
    #"This Sheet_Sheet" = #"Filtered Rows"{[Item="This Sheet",Kind="Sheet"]}[Data]
in
    #"This Sheet_Sheet"

     

     Hope this helps point you in the right direction.

     

    Regards,

    Kim

KNP's avatar
KNP
Super User
6 years ago

Hi cristianml ,

 

You'll need something like this to get the sheet names and then navigate to the specific sheet you need.

From what I understand you can't acheive this using Excel.CurrentWorkbook().

 

let
    Source = Excel.Workbook(File.Contents("YourFilePath"), null, true),
    #"Filtered Rows" = Table.SelectRows(Source, each ([Kind] = "Sheet")),
    #"This Sheet_Sheet" = #"Filtered Rows"{[Item="This Sheet",Kind="Sheet"]}[Data]
in
    #"This Sheet_Sheet"

 

 Hope this helps point you in the right direction.

 

Regards,

Kim

  • cristianml's avatar
    cristianml
    Post Prodigy
    6 years ago

    Hi KNP ,

     

    thanks ! works perfect ! now I like to solved the other part that is change the path so when i share the file to other user can use it without change the hardcoded path:

     

    File.Contents("C:\Users\cristian\Desktop\Actual vs Forecast\Macro Actual vs Forecast.xlsm")

     

    but I will try this with filepath from cell value. 🙂

     

    Thanks !!

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      This is what I do to change paths easily:

       

      1. Add a tab to the workbook

      2. Put the path in a cell and name that cell as "dir"

      3.  Load it to power query, the query should be named "dir"

      4. Convert to List

      5. Add {0} at the end of the previous step, that will conver the list to a value by taking yus the first value in the list

      6.  Replace "YourFilePath"

      Excel.Workbook(File.Contents("YourFilePath"), null, true)

      with:  (dir &"\filename.xlsx")

       

      7. Now the path is dynamic, you can edit it in Excel and it will update in power query