Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

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

  1. #1
    Board Regular
    Join Date
    Oct 2011
    Posts
    344
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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


    G&T - Get Data from Range without converting it to a Table?
    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

  2. #2
    Board Regular sandy666's Avatar
    Join Date
    Oct 2015
    Posts
    1,678
    Post Thanks / Like
    Mentioned
    10 Post(s)
    Tagged
    0 Thread(s)

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

    define name and use it in new Query, eg.
    Code:
    let
        Source = Excel.CurrentWorkbook(){[Name="DefName"]}[Content]
    in
        Source
    I know you know but I don't know what you know



    Post your Excel version (PC / Mac?), also link to shared excel file with representative example of source data and expected result.
    impossible things we do on the spot. for miracles you need to wait for a while

  3. #3
    Board Regular
    Join Date
    Oct 2011
    Posts
    344
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    Quote Originally Posted by sandy666 View Post
    define name and use it in new Query, eg.
    Code:
    let
        Source = Excel.CurrentWorkbook(){[Name="DefName"]}[Content]
    in
        Source
    Brill thanks Sandy!

  4. #4
    Board Regular sandy666's Avatar
    Join Date
    Oct 2015
    Posts
    1,678
    Post Thanks / Like
    Mentioned
    10 Post(s)
    Tagged
    0 Thread(s)

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

    U'r welcome

    btw. use Reply not Reply With Quote
    Last edited by sandy666; Apr 16th, 2019 at 01:21 PM.
    I know you know but I don't know what you know



    Post your Excel version (PC / Mac?), also link to shared excel file with representative example of source data and expected result.
    impossible things we do on the spot. for miracles you need to wait for a while

  5. #5
    Board Regular
    Join Date
    Nov 2016
    Posts
    84
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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 by peter789; Apr 17th, 2019 at 01:19 PM.

  6. #6
    Board Regular sandy666's Avatar
    Join Date
    Oct 2015
    Posts
    1,678
    Post Thanks / Like
    Mentioned
    10 Post(s)
    Tagged
    0 Thread(s)

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

    with multiple sheets define names (or print area) are not necessary because PQ recognize these ranges itself as sheet names
    I know you know but I don't know what you know



    Post your Excel version (PC / Mac?), also link to shared excel file with representative example of source data and expected result.
    impossible things we do on the spot. for miracles you need to wait for a while

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •