0

I am using query import range to pull through data from another spreadsheet which works apart from when a different type of value is needed eg. 1234 or 12345 would return correctly but values like this RIFG_PI9926 or COHJRI4426 are left blank.

I am thinking it is a formatting issue but playing around with format settings it makes no difference.

My next thought is to use the query to force results in the original format to see if that will allow them to pull through correctly.

This is what I have so far but returns blanks

=QUERY(IMPORTRANGE("url","Sheet!A2:L"),"select Col4,Col7,Col1,Col2, Col3,Col9",0)

Col7 is the problematic column

When I recreated a similar test it worked fine with the above formula which makes me think that is is definitely the formatting in some way

Stuart
  • 315
  • 1
  • 8

1 Answers1

1

this is a know QUERY issue occurring whenever you have a mixed dataset (numeric number with text strings)

sometimes it's enough just:

=QUERY({IMPORTRANGE("URL", "Sheet!A2:L")},
 "select Col4,Col7,Col1,Col2,Col3,Col9 where Col7 is not null", 0)

but mostly you will need to convert your dataset into one type:

=INDEX(QUERY(TO_TEXT(IMPORTRANGE("URL", "Sheet!A2:L")),
 "select Col4,Col7,Col1,Col2,Col3,Col9 where Col7 is not null", 0))
player0
  • 124,011
  • 12
  • 67
  • 124
  • 1
    Thank you for your amazing support. I also need to include rows with Col7 regardless of if there is a value or not. This formula unfortunately removes all rows that don't have a value at all in Col7. Do you know of a way to overcome this please? – Stuart Oct 07 '22 at 10:32
  • workaround =index(query(TO_TEXT(IMPORTRANGE("/URL","Sheet!A2:L")),"select Col4,Col7,Col1,Col2,Col3,Col9 where Col4 is not null",0)) Col4 always has data in this case – Stuart Oct 07 '22 at 10:45