How to "get data" from sheet without transforming to Table?

Hi, in my system, when i extract report from system, the layout is rather hard to see. Hence i am trying to do a Query for my staff so that can transform it to a user friendly format.
However, when i click use the "Get Data from Table/Range", it auto transform my Raw Report to a table.
I discover that i can only get from Worksheet thru the "From Excel Workbook", i do not want to use this as the Path will be invalid if i email my staff the excel.

Is it possible to get Data from the same workbook without transforming it to a Table?

This link explains pulling with PQ a file in OneDrive:
https://www.myonlinetraininghub.com/...th-power-query

Originally Posted by dflak

The query mechanism will import the information into an Excel Table, but that isn't your problem if I am reading your message correctly. You want your users to pull data from a file that is not accessible on their networks.

Consider a one drive, google drive or some other solution for pulling the data from "the cloud". Good luck with that. I haven't found a way to read a file from the cloud yet other than manually.

Yes, you are correct. I just want the data to be extract in a existing worksheet. I do not want to extract from Onedrive etc, as since its a one drive, there is chances of ppl renaming the file name, sheet name etc. Thats y i want to extract within the Book itself. My staff can save on her desktop or what

Won't @rorya's post #4 help you?

Originally Posted by Gregb11

Won't @rorya's post #4 help you?

But how do we get data from "name range"? Please enlighted me. I not that good in query

I'm not that good in PQ either, but select the named range first, then select Get Data from Table/Range.

Originally Posted by rorya

Name the range, select it, then press the From table/Range button in the Get Data section.

Hi, i have name my range. (see attached). However, when i click "get data from Table/range". It still prompt me to create table.

Am i doing it wrong?

I don't think you can do it with a dynamic named range. I've attached a workbook with the range named and show the Power Query. If you need, you can extend the Named range and in PQ remove the blank rows.

Hi, I think this matter work onli if the Data is lay out nicely?

See attached. I have extract a sample of the report. I had amend the details.
It consist of blank inbetween, merge cell etc
I had use absolute reference for the Name.

However, i still cannot Get Data from Table/Range, it still prompt me to create a table.