SQL Datasource Error Code 2146232060

[spandy34]

I am transforming data and want to link to a datasource and creating an SQL to reduce the data being brough in.  

I have created stated the SQL below and it works fine but when I want to add additional joins I get an error message

Orignal SQL that works

SELECT ahr_a.V_Visit.DeliveredVisitDate,
ahr_a.V_Client.Fullname,
ahr_a.V_Client.SSRef,
ahr_a.V_Organisation.Organisation,
ahr_a.V_Visit.Visitid,
ahr_a.V_Visit.Deleted,
ahr_a.V_Visit.ServiceLevel,
ahr_a.V_Visit.VisitType,
ahr_a.V_Visit.PlannedEntryTime,
ahr_a.V_Visit.PlannedExitTime,
ahr_a.V_Visit.DeliveredEntryTime,
ahr_a.V_Visit.DeliveredExitTime,
ahr_a.V_Visit.VarianceReason,
ahr_a.V_Visit.ExitDateTime,
ahr_a.V_Visit.EntryDateTime
FROM ahr_a.V_Client
INNER JOIN ahr_a.V_Visit ON ahr_a.V_Client.ClientId = ahr_a.V_Visit.ClientId

INNER JOIN ahr_a.V_Organisation ON ahr_a.V_Visit.OrganisationID = ahr_a.V_Organisation.Organisationid
WHERE (((ahr_a.V_Visit.ExitDateTime)>'01/02/2023'))

SQL Additional Joins where I get error message 

DataSource.Error: Microsoft SQL: Incorrect syntax near the keyword 'FROM'.
Incorrect syntax near 'ahr_a'.
Details:
DataSourceKind=SQL
DataSourcePath=xssth-sql-26\sql106;ContrOCC_IAS_Live_DataMart
Message=Incorrect syntax near the keyword 'FROM'.
Incorrect syntax near 'ahr_a'.
ErrorCode=-2146232060
Number=156
Class=15

Here is the SQL that doesnt work - what am I doing wrong?  I am only new to SQL

SELECT ahr_a.V_Visit.DeliveredVisitDate,
ahr_a.V_Client.Fullname,
ahr_a.V_Client.SSRef,
ahr_a.V_Organisation.Organisation,
ahr_a.V_Visit.Visitid,
ahr_a.V_Visit.Deleted,
ahr_a.V_Visit.ServiceLevel,
ahr_a.V_Visit.VisitType,
ahr_a.V_Visit.PlannedEntryTime,
ahr_a.V_Visit.PlannedExitTime,
ahr_a.V_Visit.DeliveredEntryTime,
ahr_a.V_Visit.DeliveredExitTime,
ahr_a.V_Visit.VarianceReason,
ahr_a.V_Visit.ExitDateTime,
ahr_a.V_Visit.EntryDateTime,
ahr_a.V_cost.Cost,
ahr_a.V_Actual.CarePackageLineItemId,
FROM ahr_a.V_Client
INNER JOIN ahr_a.V_Visit ON ahr_a.V_Visit.ClientId = ahr_a.V_Client.ClientId
INNER JOIN ahr_a.V_Actual ON ahr_a.V_Actual.CarePackageLineItemId = ahr_a.V_CarePackageLineItem.CarePackageLineItemId
INNER JOIN ahr_a.V_cost ON ahr_a.V_cost.PricingAgreementId = ahr_a.CarePackageLineItem.PricingAgreementId
INNER JOIN ahr_a.V_Visit ON ahr_a.V_Visit.ActualId = ahr_a.V_Actual.ActualId
INNER JOIN ahr_a.V_Visit ON ahr_a.V_Visit.OrganisationId = ahr_a.V_Organisation.OrganisationId
WHERE (((ahr_a.V_Visit.ExitDateTime)>'01/02/2023'))

[BA_Pete]

Hi @spandy34 ,

If the only reason you're trying to push SQL through Power Query is to limit incoming rows (and assuming all tables are on the same SQL DB), then I'd strongly recommend doing this all in Power Query (no SQL). Power Query will fold your final query to the SQL source, thus limiting your imported data.

https://learn.microsoft.com/en-us/power-query/power-query-folding 

Try it yourself:

In PQ, connect to your SQL source and import your V_Visit table.

Select the [ExitDateTime] column and add a date filter to after 01/02/2023.

Right-click the filter step in your APPLIED STEPS list on the right and select 'View Native Query' - You will see that PQ has written your SQL in the background to send to the source, so you'll never actually import any dates before your filter date.

To expand this for your extended scenario, you would:

Connect to your SQL source and import all of your required tables.

Select your V_Visit table and filter on [ExitDateTime] > 01/02/2023.

Now select your V_Client table and INNER merge all the other tables onto this.

Right-click each of your other queries in the queries list on the left and UNCHECK 'Enable Load', leaving only your V_Client query with all the others merged onto it enabled.

Now, if you right-click the last step of this query and select 'View Native Query' again, you should see everything you wanted to do in SQL above auto-generated by PQ to be sent to the source.

Pete

[spandy34]

Hi Pete

I really appreciate your response. When you refer to 'Now select your V_Client table and INNER merge all the other tables onto this.'

How would I do this please ?

[BA_Pete]

So, once you've connected to each of your tables in Power Query, they should all be listed as queries down the left-hand-side.

Select your V_Client query, then go to the Home tab > Merge Queries to open the merge dialog.

Your V_Client query will show in the top section of the dialog, and you can select the second query to merge onto it from the dropdown list below that:

Once you've selected your second query, let's say it's V_Visit, you then select the columns from each preview window to make the join on by just clicking on each of them, and select the join/merge type from the dropdown at the bottom:

So, in your scenario, you would click on [ClientId] in both the top and bottom preview windows, and also select INNER from the dropdown.

Hit OK to perform the merge, then expand the columns that you want from the second table from the nested table column that results:

Rinse and repeat for each of the other queries you want to merge onto this table.

Once done, make sure to UNCHECK 'Enable Load' on each of the 'second' queries that you merged onto this main one so they don't all get sent to the model.

Pete