G&T - Get Data from Range without converting it to a Table?

Melimob

Active Member
Joined
Oct 16, 2011
Messages
396
Office Version
  1. 365
Hi

I thought I had a simple question but can't seem to find the answer on the internet..

I have a range which I don't want to convert to a table because it will frustrate other users of the file.

I want to be able to get data from this worksheet/range but it always converts it to a table to do so?

Is there any way round this?

Many thanks
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
define name and use it in new Query, eg.
Code:
[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="[COLOR="#0000CD"][B]DefName[/B][/COLOR]"]}[Content]
in
    Source[/SIZE]
 
Upvote 0
Another sneaky way on a simple spread sheet is to set your range to the print area (this works well if you are importing from multiple identical spread sheets e.g. if each year/region etc is on separate tabs or even workbooks). This is a predefined range and can be loaded into power query.
If the size of your data range is likely to expand go old school and set it to a dynamic range. An Internet search will quickly lead you to a number of ways of achieving this.
Peter
 
Last edited:
Upvote 0
with multiple sheets define names (or print area) are not necessary because PQ recognize these ranges itself as sheet names
 
Upvote 0
I know that this is an aged topic, but I couldn't find what I needed and found this solution and wanted to share just in case someone is looking for the same thing.
__________________________

I found that this worked.

Start a [Blank Query]
Data > Get Data > From Other Sources > Blank Query

Open [Advanced Editor]
Home > Advanced Editor

Then copy and paste the code below. Change "NamedRange" to the range you want it to be.

Power Query:
let
    Source = Excel.CurrentWorkbook(){[Name="NamedRange"]}[Content]
in
    Source
 
Upvote 0
That’s the same thing that sandy posted in post 2. ;)
 
Upvote 0
I know that this is an aged topic, but I couldn't find what I needed and found this solution and wanted to share just in case someone is looking for the same thing.
__________________________

I found that this worked.

Start a [Blank Query]
Data > Get Data > From Other Sources > Blank Query

Open [Advanced Editor]
Home > Advanced Editor

Then copy and paste the code below. Change "NamedRange" to the range you want it to be.

Power Query:
let
    Source = Excel.CurrentWorkbook(){[Name="NamedRange"]}[Content]
in
    Source
This will work fine, and in fact can be used from within the Worksheet itself as long as the entire range is selected and the name of the range appears in the address box to the left of the Formula Bar:
1678126413720.png

Using Data -> From Table/Range from the above yields this without turning the range into a Table:
1678126529530.png

Interestingly, using 365 a single can be pulled into a Query by right clicking on the cell and selecting Get Data from Table/Range..... This will automatically generate the Named Range FromArray_1 (or 2 or whatever if you do it more than once!), which will put the cell into a one row, one column table with the column name Column1.
Why would you want to do that you ask? Try this formula in a Workbook that's been saved:
Excel Formula:
=TEXTBEFORE(CELL("filename",A1),"[")
It will provide the file's Folder name. and changing the M Code to
Power Query:
= Excel.CurrentWorkbook(){[Name="FromArray_1"]}[Content]{0}[Column1]
will provide a variable that can then be used to pull in other files in that directory, or the formula can be adjusted to point to the right place, or forget the formula and just type in the location. That way if you share the files or move them, they'll continue to work!
 
Upvote 0

Forum statistics

Threads
1,223,365
Messages
6,171,654
Members
452,415
Latest member
mansoorali

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top