cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
cristianml
Responsive Resident
Responsive Resident

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 somehow I think I need to use some of these but not sure how to combine them properly or what I missing :

 

Source = Excel.CurrentWorkbook() + 

{[Name="FT Actual"]}[Content] or  Kind="Sheet"]}[Data],  

 

ADVANCE EDITOR.jpg

 

Note: I don´t need to create the normal get data from query, this is a special requirement that I need to solve in order to take the data from the current workbook and the specific sheet. 

Any ideas ?

Thanks 

 

 

 

1 ACCEPTED SOLUTION
KNP
Super User
Super User

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

Have I solved your problem?
Please click Accept as Solution so I don't keep coming back to this post, oh yeah, others may find it useful also ;).
chrome-9xf-Zagzel-B

If you found this post helpful, please give Kudos.
It gives me a sense of instant gratification and, if you give me Kudos enough times, magical unicorns will appear on your screen.
If you find my signature vaguely amusing, please give Kudos.
KIfp67uy-Sr
Proud to be a Super User!PBI-Super-User-Rank-30x30-1x

View solution in original post

2 REPLIES 2
KNP
Super User
Super User

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

Have I solved your problem?
Please click Accept as Solution so I don't keep coming back to this post, oh yeah, others may find it useful also ;).
chrome-9xf-Zagzel-B

If you found this post helpful, please give Kudos.
It gives me a sense of instant gratification and, if you give me Kudos enough times, magical unicorns will appear on your screen.
If you find my signature vaguely amusing, please give Kudos.
KIfp67uy-Sr
Proud to be a Super User!PBI-Super-User-Rank-30x30-1x
cristianml
Responsive Resident
Responsive Resident

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 !!

Helpful resources

Announcements
August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power BI Dev Camp Session 24 without aka link and time 768x460.jpg

Ted's Dev Camp - July 28, 2022

Watch Session 24 of Ted's Dev Camp along with past sessions!

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors