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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Hussard
Frequent Visitor

SQL Query to Power BI

Hi all,

 

At first I made a view in SQL SERVER, and all is OK.

 

Now I want to create directly into Power BI the same query I've done in SQL Server but from the whole tables.

 

Here is the SQL statement:

 

SELECT pc.id ,count(*) cpt, p.state
FROM Product AS p INNER JOIN Prices AS pp ON p.Id = pp.IdProduct INNER JOIN Catalog AS pc ON p.IdCatalog = pc.Id
WHERE ISNULL(pp.tariff, 0) <> 1 AND pc.version<> - 1 and pc.Status<>2
group by pc.id, p.state
order by pc.id

 

I tried to merge queries in query editor and also create some fields but ... I don't know what is the best method to do that.

 

I think this issue is quite easy to solve but I don't know how.

 

Thanks !

 

Sébastien

1 ACCEPTED SOLUTION

DirectQuery is not the best mode to get to know Power BI. It is better to work with Import in this case.

 

How to implement such task via Power BI:

1. Go to an advanced editor of a blank query.

2. Reference the first table and filter it as you mentioned in SQL:

    ISNULL(pp.tariff, 0) <> 1 AND pc.version<> - 1 and pc.Status<>2

3. Repeat the previous action to last two tables.

4. Use merge to join 1st and 2nd.

5. Merge result of the previous action with 3rd.

6. Do grouping

7. Do ordering

 

Regards,

Ruslan

View solution in original post

10 REPLIES 10
vanessafvg
Super User
Super User

@Hussard hi  a bit confused about what you are asking?  are you struggling with the approach you have tried is there a problem, if so what is the problem exactly.

 

are you saying you want to import the whole table into power bi and then apply those conditions?  

 

can you not just post your query into power bi and use your sql code?





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!




Hi,

 

Sorry I was unclear 😞

 

First, I want to connect my 3 tables in direct query mode. (in my current report I have created a view in SQL Server, and I connect from this view in Power BI. It works, but I want to do the same things directly in Power BI)

 

How can I merge the data, to have the same result as the query above ?

 

Sébastien

Hi @Hussard,

 

I think you've connected to 3 tables via DirectQuery and you want to create a new 4 table in Power BI, which will be a result of joining of these 3 tables?

 

Why you simply not connect to a database with specifying your entire query in Advanced options -> SQL statement like below?

 

dqdq

 

Regards,

Ruslan

Yes, you are right, this is exactly what I want to do !

 

Yes I found here this solution (it's ok) but I wanted to use the merge fonction and count to have a clearer view of Power BI.

 

I don't know if it's possible but your solution is good and works

 

Sébastien

DirectQuery is not the best mode to get to know Power BI. It is better to work with Import in this case.

 

How to implement such task via Power BI:

1. Go to an advanced editor of a blank query.

2. Reference the first table and filter it as you mentioned in SQL:

    ISNULL(pp.tariff, 0) <> 1 AND pc.version<> - 1 and pc.Status<>2

3. Repeat the previous action to last two tables.

4. Use merge to join 1st and 2nd.

5. Merge result of the previous action with 3rd.

6. Do grouping

7. Do ordering

 

Regards,

Ruslan

webportal
Impactful Individual
Impactful Individual

Hi all,

I came across a similar issue and this post has helped me,

However, I'll try an R script to grab all the static data into a .csv (cheaper than Excel) and build a flow from that.

Then, in Power BI Desktop, I'll import the most recent data, as of a certain date and keep that refreshed every day.

I don't understand your first sentence, why Direct Query isn't the best mode ? I believe import and direct query are quite similar except data imported or not ?

 

Ok I have done the exercise but i can't save... (in french sorry) : "failed to save the modifcation to server..." Maybe it's because I'm in direct query mode

 

image.png

@Hussard,

 

DirectQuery has limited functionality. 

 

See similar thread - http://community.powerbi.com/t5/Desktop/direct-query-vs-import/m-p/112255#M47331

Detailed article from Radacad team - http://radacad.com/directquery-live-connection-or-import-data-tough-decision

 

Regards,

Ruslan

OK I close the topic because in Import Mode I have no issues !

 

Thanks a lot for your time on that, and also for the links ! It will be very helpful for me

 

I didn't know some of differences mentionned into these links

 

Sébastien

@Hussard,

 

It is good to hear. You are welcome.

 

Regards,

Ruslan

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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