Merging PowerBI Table during SQL import query

[Anonymous]

Hi,

I have Table A in PowerBi as an import Query, I have Table B which is the result of a ODBC connection into our data warehouse using SQL. I want to a right outter join from Table A into Table B to get Table C (so all data from The datawarehouse (table B) that match the records from Table A are imported). I'm currently doing this as a merge query in the query editor but I am getting a lot of blank values in Table C in the columns that should come from the DW. What I think is happening is PowerBI is trying to import the entire referenced SQL query from the datawarehouse, and then filtering for Table A which is obviously filling up my diskspace before getting to the merge line. Is there anyway to incorporate Table A into the SQL query for the 'Source' of Table B so the DW is doing the processing?

[Anonymous]

Hi @Anonymous 

Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution. More people will benefit from it. Or you are still confused about it, please provide me with more details about your table and your problem or share me with your pbix file from your Onedrive for Business.

Best Regards,

Rico Zhou

[Anonymous]

Hi,

2.  Build a temp table A in SQL and merge it with table B in SQL then import the result table into Power BI.

How do I build a temp SQL table using data from another data source (excel on shared drive) in powerbi? 

[Anonymous]

Hi @Anonymous 

Due to your data source in SQL is huge, you may do transform in data source.

Here I have some advice.

1.  You can try to build a temp table C which only has the value you need in table B and try to use advance option to import it into Power BI to merge with tableA.

2.  Build a temp table A in SQL and merge it with table B in SQL then import the result table into Power BI.

Best Regards,

Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

[amitchandak]

@Anonymous , if both tables are from the same DB connection, you should write a query and bring in data. You have option that option in advance to write SQL

[vanessafvg]

can't you write a sql query instead of just pulling the data through in the advanced section of the query connnection?

[Anonymous]

That's what I'm asking, Table A isn't in the DW it's just an excel file on a shared drive. 

[vanessafvg]

ah i see! the only way to push it to the server is to have it in sql unfortunately.