Forum Discussion

pgelli's avatar
pgelli
Microsoft Employee
6 years ago

Get Table information from dataset Programmatically

Hi All,   Is there a way to programatically get the information of datasource & tables used in a dataset (like some kind of metadata).  I saw there is a GetDataSet API, but that works on the datas...
Need Help
pgelli's avatar
pgelli
Microsoft Employee
6 years ago

Sorry for the confusion lbendlin .
My need is to programatically find the list of source tables which are backing a given powerbi report.

lbendlin's avatar
lbendlin
Super User
6 years ago

That's what I gave you with the DMV query.  You need to take that M code and parse it. It contains the server, database and query.

 

  • pgelli's avatar
    pgelli
    Microsoft Employee
    6 years ago

    lbendlin , I am afraid I am getting different results for that query:

     


    These are not the tables present in our Source. Am I missing something? 

  • lbendlin's avatar
    lbendlin
    Super User
    6 years ago

    Most likely you connected to the wrong endpoint. Different dataset maybe?

  • pgelli's avatar
    pgelli
    Microsoft Employee
    6 years ago

    I verified that I am looking at right dataset. However, PowerBI report is built using "import" and not "direct query".
    Does it make a difference lbendlin ?

  • lbendlin's avatar
    lbendlin
    Super User
    6 years ago

    No, once you access the dataset it doesn't matter much if  the query connection type is import/ direct query or mixed.  

     

    I just tried it with one of my direct query datasets and got the expected result. 

     

    As expected it will not work at all with live connections.

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    I had the same problem as pgelli when using lbendlin 's suggestion of SELECT * FROM $SYSTEM.TMSCHEMA_PARTITIONS; I was receiving the DAX QueryDefinitions and not the M Query defintions (super useful, just not what I needed).  What solved it for me was instead using  SELECT * FROM $System.DISCOVER_M_expressions.  Hopefully this saves someone else some time for this use case.