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;

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.

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

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

Hi anithat Curious to know how this works? I'm facing a similar issue. I have built a query in mysql client and want to copy paste this. My questions are:

1) First I have to connect to source and load tables? or

2) Write the query in advanced editor and then connect to source? Confused how it works.

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.

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

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