Forum Discussion

aspardo's avatar
aspardo
Regular Visitor
10 years ago

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

Started using Power BI on the recommendation of a friend. It looks well made and powerful, so I hope to stick with it.   I am pretty decent with SQL and can write some lengthy queries. My goal is t...
Need Help
calerof's avatar
calerof
Icon for Impactful Individual rankImpactful Individual
7 years ago

Hi anithat  and supporting team , ElizabethTachji ,

 

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

 

But I'm still getting this error:

 

Could you please help?

 

Best regards,

 

Fernando

 

 

alwweb's avatar
alwweb
Icon for Advocate II rankAdvocate II
6 years ago

BB9 

If you are moving all of the required data into a single table in another database using a single query or stored proc, you can simply put that query or execution of that stored procedure into the query option area when you expand Advanced during the import.  This is where a SQL query goes.  After the import screens, when in the Source step and you go into the Advanced Editor in the Power Query Editor, this would be where you would write an M query to limit the data being brought in.

 

Finally, you could create a view in the same database as the tables and write a SQL statement to retrieve the data from the view.

 

Having the proper SQL indexes in the source database will help the performance drastically.

 

Please let me know if any of this helps.

 

calerof 

I'm not sure if you saw my earlier response to your question, but you are trying to put an M query where the SQL query is supposed to go.   You would put this query into the Advanced query of the data source after the import has happened when you are in the Power Query Editor, with the large table you want to limit the import highlighted on the left side, and the connection string step selected on the right side, click Advanced Editor as shown below ...

 

The other option would be to write the SQL statement as the following if using MS SQL Server as the source (other sources have different syntax)

SELECT * --or list out your column names that you want

FROM dbo.RIN1

WHERE DocDate > '20171231';

 

 

Please let me know if this helps.  I know it has been a while since the original questions were posted.

  • newpi's avatar
    newpi
    Icon for Helper V rankHelper V
    6 years ago

    Anonymous Not clear if you are asking a question. From what I understand you want to merge two tables using a query in Big Query and bring in only that data right which is 3800 rows. What you can do is write your SQL query in Google BQ Console and then save that data as a view under a project. Then in Power BI, connect to that view instead of those 2 tables.

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    How can I edit the query when I'm using the BigQuery connector?

    The bigQuery connector just allow me to select a table, but I need to make a different sql to correlate 2 tables with several gigabytes of data. The result has only 3800 rows and that is what I want to bring to powerBI.

     

    I cannot change my odbc connections, but I can use the BigQuery connector.

     

    Can I edit the SQL in PowerQuery advanced editor?

     

    Thanks a lot

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Thank you for the quick answer newpi .

     

    I know I can proceed as you described. My question is if there is a way do specify the SQL in the connector configuration.

    As example, for oracle we can write in advanced editor:

    let
    Source = Oracle.Database("dbHost/db",[HierarchicalNavigation=true, Query="select field from table where condition"] )
    in
    Source

     

     

    Can I do the same with bigquery connector? how? I tried the same but the "Query" parameter does not work 😞

     

    I'm using:

    let

       Source = GoogleBigQuery.Database([BillingProject="projectid"])
          {[Name="projectid"]}[Data]
             {[Name="myDataset",Kind="Schema"]}[Data]

                 {[Name="myTable",Kind="Table"]}[Data]

    in

       Source

     

    Documentation is not clear nor can't find the possible options for the connector

     

    Thanks to all