Typically I connect to our SQL Server in Import mode, grab the tables that I need, and use my Personal Gateway to refresh the data each morning.
In a new report that I built, I used a SQL statement to pull in fields that aren’t normally in the table that I use, but when the scheduled refresh ran this morning, my report went blank. Since the only difference in this report is that SQL statement, I’m guessing that it’s the culprit.
I was thinking that it could be that I’m using Import rather than Direct Query, but if I change it then I can’t manipulate the data in Power Query.
Hi , we aim to consistently improve the topics being posted on the forum to help you in getting a strong solution faster. While waiting for a response, here are some tips so you can get the most out of the forum and other Enterprise DNA resources.
Use the forum search to discover if your query has been asked before by another member.
When posting a topic with formula make sure that it is correctly formatted to preformatted text </>.
Use the proper category that best describes your topic
Provide as much context to a question as possible.
Include demo pbix file, images of the entire scenario you are dealing with, screenshot of the data model, details of how you want to visualize a result, and any other supporting links and details.
In the new report you built , do you configure to use import mode or direct query ? When you use import mode then you will be able to modify the data in Power Query
If it is Direct query then you will have to modify the data from the sql data query view.
In all you need to look at the data gateway and be sure that the connection to the source data base is fine both for the import and direct query mode.
1/2. Import mode. It’s really the only way that I know how.
3. I use my personal gateway for everything, but I have never used a SQL query before. It appears that the new data only pulls in when I refresh in Power BI desktop and then I have to push it to the Service again, which is not what I want to happen.
I have tested the same using Microsoft SQL statement and Personal Gateway. I am able to refresh successfully in service without any issues. So, using SQL statement and personal gateway is not the issue. Neither any Microsoft official document talks around it.
Also, Personal GateWay only supports Import, so Direct Query is not an option with PG.
Problem is somewhere else but can’t depict from your inputs. Please check if there are any data issues or any Report Filters or RLS that may be affecting you.
Can you try with Enterprise Gateway assuming you have PRO account.
Hi @Jewelsdpu, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box. Also, we’ve recently launched the Enterprise DNA Forum User Experience Survey, please feel free to answer it and give your insights on how we can further improve the Support forum. Thanks!
Hi All,
You were right…it wasn’t SQL at all. It was a very strange issue that had to do with a TRUE/FALSE column that I was using to filter the data. For some reason, that made my entire dataset go blank on the service even though it worked on PowerBI Desktop. I changed the column type to whole number so that the TRUE values turned into 1s and BOOM! It worked. Very strange, indeed.
Thanks for your help on this.
~Julie
