Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
Ash1
Frequent Visitor

Modify query after datasource is created

Hello 

 

Is there a way to modify the datasource from which data has been loaded to PowerBI?

 

Initially when I have created a report, I used Import query option to retrieve all the records, with all the columns in a SQL Table. The table has more than 65 M records and now when I try to refresh the query, it fails with memory not available erorrs.

 

What I'm trying do it to limit to only the columns that I need to get the report functional. so I tried,  to add the query wiht the where clause 

 

Edit Queries -> Data Source Settings 

 

However the query window is greyed out. Is ther a way out without building a new report altogather?

 

Thanks

8 REPLIES 8
Phil_Seamark
Microsoft Employee
Microsoft Employee

Hi @Ash1

 

If you can get into the Query Editor try clicking this button

 

Query Editor.png

 

Hopefully it brings up the query window where you can replace your query with one that contains just the columns you need and perhaps a WHERE clause that restricts your rows to exactly what you need.

 

 


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Thanks for the reply,

 

It allows me to update the qeury, but I'm running into issues when I'm trying to apply the changes 

 

Error: The key didn't match any rows in the table

Details: 

Key= Record

Table = Table 

 

 

~Ashwin

Hi @Ash1,

You click the button as @Phil_Seamark posted, you will get the Navigator like the following screenshot.


1.PNG

You use the same SQL Statement in highlighted line area as in SQL Server, it return the error message? It's weird. Please close the Edit Query and open it, and check if it works fine.

Thanks,
Angelia

Oh ok, so maybe edit the query to limit the rows and use the Query Editor to remove the columns.  

 

Can you revert the query back to what it was but just put a TOP 100 in the SELECT ?


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

No luck.

 

I tried selecting all the columns with the dateadd fileter to limit rows. I'm seeing same error 

 

The key didn't match any rows in the table

are you getting the same issue if you run the query in sql?





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




No. Query runs fine on SQL Server. 

Hi @Ash1,

Have you resolved your issue? Please mark the right reply as answer. If you have not, don't hesitate to ask.

Thanks,
Angelia

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.