Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
simp
Helper I
Helper I

Connect to Data in Excel Table not Sheet

Hello,

Whenever i try to connect to my excel file, it shows all range from A1 to end,

I want to connnect to data in specific table range name,

Is it possible, and how to do it

Thank you

1 ACCEPTED SOLUTION
MarcelBeug
Community Champion
Community Champion

First get your definitions clear.

A range is not the same as a table.

So first create your named range of - better - your table in your Excel workbook.

Save the workbook.

Connect to the workbook (using "From Excel"), which will give you a list of usable objects, including your table (or range) and the worksheet(s).

Now drill down to the table (or range).

Specializing in Power Query Formula Language (M)

View solution in original post

4 REPLIES 4
MarcelBeug
Community Champion
Community Champion

First get your definitions clear.

A range is not the same as a table.

So first create your named range of - better - your table in your Excel workbook.

Save the workbook.

Connect to the workbook (using "From Excel"), which will give you a list of usable objects, including your table (or range) and the worksheet(s).

Now drill down to the table (or range).

Specializing in Power Query Formula Language (M)

Hi @MarcelBeug ,
For some reason I do not get a list of my tables from the excel sheet. Would you mind posting the generated M code for how it connects to the source?

 

Thanks

Michael

Solved my problem:

Seems I was working with old workbook file types. After converting them to the latest Excel workbook types the tables where visible in the natigation step of Power Query. 

Hi @MarcelBeug

 

Thank you for your feedback,

This is wierd,

Today i can see the table category (not just sheet)

So, problem solved

Thank you

 

 

 Capture.PNG

 

update: i realized that this is because my file is protected with such DRM so the table category is not showing (i must open it first to have acces to the file from pbi)

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.