Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
After doing a direct query on a database :
-- POPULATE PROJECT REFERENCE AND TOTAL WATER OFFERED --
WITH WaterOffered AS (
SELECT
Project_Reference,
SUM(Water_Total_AV) AS Total_Water_Offered_AV
FROM [Last_Mile].[dbo].[vwWater_AV_Current]
WHERE Effective_To_Date IS NULL
GROUP BY Project_Reference
),
-- DOMESTIC AMOUNT PAID --
DomesticWaterPaid AS (
SELECT
Project_Reference,
SUM(Asset_Value_Paid) AS Domestic_Water_AV_Paid
FROM [Last_Mile].[dbo].[vwWater_Asset_Value_Paid_Domestic]
GROUP BY Project_Reference
),
-- EARLIEST ACCEPTED DATE PER PROJECT --
EarliestAcceptedDate AS (
SELECT
Project_Reference,
MIN(Acceptance_Date) AS Acceptance_Date
FROM [Last_Mile].[dbo].[Adopt_TbleProject_Details]
GROUP BY Project_Reference
),
-- EARLIEST CONNECTION DATE PER PROJECT --
EarliestWaterConnection AS (
SELECT
pd.Project_Reference,
MIN(bwsp.Meter_Installation_Date) AS Earliest_WaterClean_Conn
FROM [Last_Mile].[dbo].[Adopt_TbleProject_Details] pd
INNER JOIN Billing_Water_Supply_Points bwsp
ON pd.Project_Reference = bwsp.Project_Reference
WHERE bwsp.Water_Flag = 'YES'
GROUP BY pd.Project_Reference
),
-- AGED DATA FOR DOMESTIC --
AgeData AS (
SELECT
pd.Project_Reference,
bwsp.LMAM_Water_Supply_Point_No,
ead.Acceptance_Date,
bwsp.Occupancy_Date,
DATEDIFF(dd, ead.Acceptance_Date, bwsp.Occupancy_Date) AS AgeDays,
CASE
WHEN DATEDIFF(dd, ead.Acceptance_Date, bwsp.Occupancy_Date) IS NULL THEN 'Not Connected'
WHEN DATEDIFF(dd, ead.Acceptance_Date, bwsp.Occupancy_Date) >= 0
AND DATEDIFF(dd, ead.Acceptance_Date, bwsp.Occupancy_Date) <= 50 THEN '0-50'
WHEN DATEDIFF(dd, ead.Acceptance_Date, bwsp.Occupancy_Date) >= 51
AND DATEDIFF(dd, ead.Acceptance_Date, bwsp.Occupancy_Date) <= 125 THEN '51-125'
WHEN DATEDIFF(dd, ead.Acceptance_Date, bwsp.Occupancy_Date) >= 126
AND DATEDIFF(dd, ead.Acceptance_Date, bwsp.Occupancy_Date) <= 250 THEN '126-250'
WHEN DATEDIFF(dd, ead.Acceptance_Date, bwsp.Occupancy_Date) >= 251
AND DATEDIFF(dd, ead.Acceptance_Date, bwsp.Occupancy_Date) <= 1000 THEN '251-1000'
WHEN DATEDIFF(dd, ead.Acceptance_Date, bwsp.Occupancy_Date) > 1000 THEN '1001+'
ELSE 'Error'
END AS AgeBuckets
FROM [Last_Mile].[dbo].[Adopt_TbleProject_Details] pd
INNER JOIN Billing_Water_Supply_Points bwsp
ON pd.Project_Reference = bwsp.Project_Reference
INNER JOIN EarliestAcceptedDate ead
ON pd.Project_Reference = ead.Project_Reference
WHERE pd.Project_Type != 'Commercial'
AND bwsp.Water_Flag = 'YES'
)
-- FINAL SELECT TO OUTPUT ALL DATA --
SELECT
wd.Project_Reference,
wd.Total_Water_Offered_AV,
dw.Domestic_Water_AV_Paid,
ead.Acceptance_Date,
ewc.Earliest_WaterClean_Conn,
ad.LMAM_Water_Supply_Point_No,
ad.Occupancy_Date,
ad.AgeDays,
ad.AgeBuckets
FROM WaterOffered wd
LEFT JOIN DomesticWaterPaid dw
ON wd.Project_Reference = dw.Project_Reference
LEFT JOIN EarliestAcceptedDate ead
ON wd.Project_Reference = ead.Project_Reference
LEFT JOIN EarliestWaterConnection ewc
ON wd.Project_Reference = ewc.Project_Reference
LEFT JOIN AgeData ad
ON wd.Project_Reference = ad.Project_Reference;
its showing me an error while applying this query in BI :
Microsoft SQL: Incorrect syntax near the keyword 'WITH'. Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon. Incorrect syntax near ','. Incorrect syntax near ','. Incorrect syntax near ','. Incorrect syntax near ','. Incorrect syntax near ')'.
Query works fine in SSMS and also in BI - power query loads an ouput for it but when i hit Apply and close it shows me this annoting error.
can someone tell me whats wrong here !!!!! please ❤️
Solved! Go to Solution.
@Dwivedi15
Connect to the SQL server through SSMS or Azure Data Studio and paste this query and ensure it runs without error, it will highlight the lines where you find errors.
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
Hi @Dwivedi15
I hope this information is helpful. Please let me know if you have any further questions or if you'd like to discuss this further. If this answers your question, please Accept it as a solution and give it a 'Kudos' so others can find it easily.
Thank you.
Hi @Dwivedi15
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank you.
Hi @Dwivedi15
May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.
Thank you.
Remove ; at the end of your query
Hi @Dwivedi15
Thank you for reaching out microsoft fabric community forum.
Not all SQL expressions are supported in power bi, they may load an output while previewing but they wont load into Power BI and will always generate an error
Common table expression is one of those queries which are not supported by power bi.
We have to work around this limitation
Thanks and regards
@Dwivedi15
Review your semi colons and proper use of schema.
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
Do you have any idea from where can i check it ?
@Dwivedi15
Try to add a semicolon (;) before WITH WaterOffered AS (
;WITH WaterOffered AS (
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
No not worked -- now its giving this error :
Microsoft SQL: Incorrect syntax near ';'. Incorrect syntax near ')'.
@Dwivedi15
Connect to the SQL server through SSMS or Azure Data Studio and paste this query and ensure it runs without error, it will highlight the lines where you find errors.
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
I have ran this query in SSMS - no errors - ran successfully there
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
15 | |
12 | |
8 | |
8 | |
7 |
User | Count |
---|---|
15 | |
13 | |
9 | |
7 | |
6 |