Forum Discussion

AndrewWestran's avatar
AndrewWestran
Helper I
2 years ago

Dataflow and PowerBI Gateway - Sourcing data dynamically

Hi all,   I have a Dataflow Gen2 which is sourcing data from an on-premises SQL Server via PowerBI Gateway. The server contains many databases, each representing a retail POS. These databases may...
Dataflows
Element115's avatar
Element115
Memorable Member
2 years ago

I was re-reading your original post and something occurred to me... have you tried to maintain a table with meta data, that is, a table that will keep track of all the DB servers and DB names as they come and go?  This would allow you to create a query that connects to the DB containing this meta data table, and using that table, you can in M select any or all of your databases and thus ingest their data.

  • AndrewWestran's avatar
    AndrewWestran
    Helper I
    2 years ago

    Hi Element115 

     

    I gather that I will be able to use the M "sql.databases" function to acquire the details of the databases that I need to access, so no additional metadata tables should be necessary.

    I do appreciate the time and thought you have taken to respond to this query. I am open to any further comments you may have to offer. 🙂

     

    Thanks and regards

    • Element115's avatar
      Element115
      Memorable Member
      2 years ago

      Interesting!  You're right, that should do the trick.  I was not aware M had such a function.  Pretty nifty. ğŸ˜Š