cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ElizabethTachji Regular Visitor
Regular Visitor

Re: New to BI. Writing SQL queries to parse data. I am obviously missing something.

Hi,

 

I would like to add here, in Power BI Desktop, query Editor, you have the option to disable the load query so the query is not refreshed.  When you disable the load, you will not slow down the performance of the servers and will improve the performance of Power BI Desktop report.

 

Cheers

Elizabeth Tachjian

Analytics Realtime

elizabeth@analyticsrealtime.com.au

Elizabeth Tachjian
calerof Member
Member

Re: New to BI. Writing SQL queries to parse data. I am obviously missing something.

Hi @anithat  and supporting team , @ElizabethTachji ,

 

I performed your procedure, only modifying the query to the table I need, as follows:

Custom query 1.png

 

But I'm still getting this error:

Custom query 2.png

 

Could you please help?

 

Best regards,

 

Fernando

 

 

calerof Member
Member

Re: New to BI. Writing SQL queries to parse data. I am obviously missing something.

The problem stated here persists for me, but I managed to solve my issue of not being able to upload on table that didn't appear as follows:

  1. The database has more than 10,000 tables
  2. In SSMS all the tables are visible
  3. I deducted after a very long week of insomnia that the problem was the limit of number of tables read with Get Data, SQL Server
  4. So I tried the procedure explained above, with no results
  5. Then I tried it with the blank query option, and voila

I think it's still not resolved using Advanced Options, so I'll leave it open for your comments.

 

Best regards,

 

Fernando

 

alwweb Regular Visitor
Regular Visitor

Re: New to BI. Writing SQL queries to parse data. I am obviously missing something.

@calerof .  At this point in the Process your query needs to be SQL.  It is in M which is used a little later in the process. 

 

Try the following and replace yourtablename wiht the name of the table that you want to retrieve. Once you have that working, you can go from there to get exactly the data that you want.

 

SELECT TOP 100  *

FROM yourtablemame;

 

 

alwweb Regular Visitor
Regular Visitor

Re: New to BI. Writing SQL queries to parse data. I am obviously missing something.

If the SQL refreshes are timing out, you are probably correct that you are pulling too much data at once, but, doing all of the joins in the SQL  may also slow it down more.

 

I am a SQL person first and a Power BI person second, so I look at things a little differently from most people on this forum.

1) I like bringing in all of the tables individually and using the relationships in Power BI rather than "flattening" my data source by doing all of the joins when pulling in the data.  This gives me more power for creating my formulas, etc inside of Power BI.

2) I would prefer to bring in one table at a time with a query for each tables (some denormalization where it makes sense by doing the joins) if I need to remove a lot of columns or some of the rows.  This way, if the SQL database is optimized properly, it will take much less time to refresh.  Rather than pulling in all of the data and then removing a bunch, we only pull what we need.

 

On your comment from page 3 that I responded to earlier.  I realized you missed where the other user was putting in the M code.  It is after the import happens, by clicking Advanced Editor on the Home tab of the Power Query Editor.

 

For books, to understand the concept of normalization that I mentioned earleir in this comment, I recommend any of the edtitions of Jan Harrington's Relational Database Design Clearly Explained.  I like the 1st edition best if you can find it in a used book store.

 

For writing T-SQL (MIcrosoft's flavor of the SQL language - stands for Transact-SQL) there are a huge number of books to choose from.  If you are going to be doing a lot of query writing and don't have an IT team to create VIEWS that already have what you need in them, I recommend taking a 3 day course.  If you like learning on your own, one of the most popular books are the ones by Itzik Ben-Gan.  Either T-SQL Querying or SQL Fundamentals.

 

I hope some of this helps.

Helpful resources

Announcements
Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Summit Australia 2019

Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

PBI Community Highlights

PBI Community Highlights

Check out what's new in the Power BI Community!

Top Ideas
Users Online
Currently online: 270 members 2,811 guests
Please welcome our newest community members: