locked
1 Request, 2 Datasources with same structure RRS feed

  • Question

  • Hi,

    I'm begging with PowerQuery. I'm looking to connect it to my ERP.

    I've different ERP SQL database, with all the same structure.

    I dont want to merge request but simply connect 1 request to my différents database :

        Source = Sql.Databases("123\sqlexpress"),
        XXX = Source{[Name="XXX"]}[Data] & ADD OTHER SOURCES HERE

    Ideally, each new sources shoud complete a column to filter data in a second time.

    Thanks for ur help !

    Ben

    Tuesday, January 16, 2018 9:39 PM

Answers

  • you're absolutely doing it right. Connect to the server and not to a specific database of that server (that's why you need to use Sql.Databases instead of Sql.Database) and what you can do from there is filter that first preview of data to only get the databases that you want, expand the 'Data' column and then filter out the 'Name' column to only get the tables that you want. The end result of that should be the same table names from both databases. After that, you should be able to simply expand the 'Data' Column of those tables and you'll end up with the data exactly how you want it.

    Now, I'm not sure that this query is foldable. If you tried doing both separately and then appending them PQ might actually fold the query and do only 1 request (sql statement) to that server instead of 2.

    Wednesday, January 17, 2018 8:05 PM

All replies

  • You could create a function that can deal with one of the servers. And then pass the names of the servers to the function to process them all. The concept is explained at my blog here. Different, but the same. https://exceleratorbi.com.au/combine-excel-workbooks-power-query-method-1/

    Self Service BI Expert using Power Pivot http://exceleratorbi.com.au

    Wednesday, January 17, 2018 8:21 AM
  • you're absolutely doing it right. Connect to the server and not to a specific database of that server (that's why you need to use Sql.Databases instead of Sql.Database) and what you can do from there is filter that first preview of data to only get the databases that you want, expand the 'Data' column and then filter out the 'Name' column to only get the tables that you want. The end result of that should be the same table names from both databases. After that, you should be able to simply expand the 'Data' Column of those tables and you'll end up with the data exactly how you want it.

    Now, I'm not sure that this query is foldable. If you tried doing both separately and then appending them PQ might actually fold the query and do only 1 request (sql statement) to that server instead of 2.

    Wednesday, January 17, 2018 8:05 PM
  • Thanks guys for ur help.

    I'll try it this weekend and will keep you informed !

    ben


    • Edited by Xperl Thursday, January 18, 2018 2:52 PM
    Thursday, January 18, 2018 2:52 PM