Import only required data records

Power BI
1

I am trying to import data from a PostgreSql source but I only want the records where a particular column starts with a specific character string.

For example I only want those records for a particular customer identified by their customer code.

I am trying to use SQL Select Where statement in the query editor but the syntax is not right.

Example: select * where customer.info LIKE ‘A0001%’ (customer.info is the column name)

Regards

Paul

2

Hi @Paul

Are you including what table you’re selecting from?

SELECT *
FROM Customer
WHERE Customer.Info LIKE ‘A0001%’

If you are, is Customer.Info the whole column name or is the Customer part the table name? If it’s the whole column name, it will likely need to be wrapped in as the period is probably a reserved character. At least it is in TSQL, I’m not familiar with Postgre.

3

Hi

Thank you for your reply.

Yes customer.info is the whole column name but it contains a variety of fields separated by a ~ character which I unpack later in the process and I am using the first five characters as the selector.

I am importing from a specific table called “processed_data new_data_translated”.

Best regards

Paul

4

Then you most likely will need to bracket it, same with your table name as this has a space. For example:

SELECT *
FROM [processed_data new_data_translated]
WHERE [Customer.Info] LIKE ‘A0001%’

Does that work for you?

5

I now get the following error.

DataSource.Error: ODBC: ERROR [42601] ERROR: syntax error at or near “[”;

regards

Paul

6

Apologies, can you try:

SELECT *
FROM “processed_data new_data_translated”
WHERE “Customer.Info” LIKE ‘A0001%’

From reading online it appears Postgre uses quotes rather than brackets. If that doesn’t work I’m out of ideas I’m afraid as not familiar with it.

7

Hi @PaulBoyes. I don’t know PostgresSQL at all, but using the LIKE operator in TransactSQL (used by Microsoft SQL Server) is a slow operator. I’d prefer to use a LEFT-= operator instead, so your WHERE clause would be something like

WHERE left(Customer.Info, 5) = ‘A0001’

Regardless, if you have access to a DBA (or someone else) who regularly accesses your database, get a copy of one of their queries that works and adjust the values as desired.

Hope it helps.
Greg

8

Hi again

I tried your suggestion, see below.

SELECT * FROM (processed_data new_data_translated) WHERE left (Customer.Info,5) = ‘A0001’

I now get the following error message

ODBC: ERROR [42601] ERROR: syntax error at or near “)”;
Error while executing the query.

Regards

Paul

9

Again, I don’t know PostgresSQL at all, but I doubt you should have brackets around your table name after the FROM. I’d follow-up with your DBA can help craft a query that works in your environment.
Greg

10

Greg

Thank you very much for your suggestions.

The problem is, of course, that there is a space in the name of the table.

I will follow up on your suggestion.

Best regards

Paul