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

[Melimob]

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

 

[sandy666]

define name and use it in new Query, eg.

[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="[COLOR="#0000CD"][B]DefName[/B][/COLOR]"]}[Content]
in
    Source[/SIZE]

 

[Melimob]

define name and use it in new Query, eg.

[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="[COLOR=#0000CD][B]DefName[/B][/COLOR]"]}[Content]
in
    Source[/SIZE]

Brill thanks Sandy!

 

[sandy666]

U'r welcome

btw. use Reply not Reply With Quote

 

[peter789]

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

 

[sandy666]

with multiple sheets define names (or print area) are not necessary because PQ recognize these ranges itself as sheet names

 

[darkwing1711]

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.

let
    Source = Excel.CurrentWorkbook(){[Name="NamedRange"]}[Content]
in
    Source

 

[RoryA]

That’s the same thing that sandy posted in post 2.

 

[jdellasala]

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.

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:

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

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:

=TEXTBEFORE(CELL("filename",A1),"[")

It will provide the file's Folder name. and changing the M Code to

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