Forum Discussion

Jitmondo's avatar
Jitmondo
Helper III
1 year ago
Solved

Function like info.View but instead shows data source for each table/query?

Hi all,

 

Currently collating a data dictionary template, great as it auto updates using some dax with variables and union.

Produces a Data Dictionary table that we can extract and link to somewhere to make a Master Data Dictionary file within Power BI

 

Problem -  I need to add in the Data Source for each table/query - I have tried:

 

  • TMDL
  • DAX Query Editor
  • M Code

Right now we can create the dictionary outside of info.view using Dax Studios DMV for measures,columns.tables, and partition but its manual and means 4 files at a time for us to update to the repository the Master Data Dictionary will run from.

 

Any ideas on how I can do this? - I am even open to having the table list and source populate as its own visual.

 

I have mangaged to get a new query with table names of all tables in file and generate a source column but Power BI wont read and populate Meta Data like that.

 

Appreciate your help as always 🙂

 

Need Help
  • danextian's avatar
    danextian
    1 year ago

    Hi Jitmondo 

     

    I don't think such a function exits. You can use INFO.PARTITIONS() in DAX query view to view query definition of a table (DAX or M) and identify the source from there.

    EVALUATE
SELECTCOLUMNS (
    INFO.PARTITIONS (),
    "Table Name", [Name],
    "Query Definition", [QueryDefinition],
    "Source Category",
        IF (
            CONTAINSSTRING ( [QueryDefinition], "let Source" )
                || LEFT ( [QueryDefinition], 3 ) = "let",
            "M",
            "DAX"
        )
)

     

4 Replies

  • danextian's avatar
    danextian
    Super User
    1 year ago

    Hi Jitmondo 

     

    I don't think such a function exits. You can use INFO.PARTITIONS() in DAX query view to view query definition of a table (DAX or M) and identify the source from there.

    EVALUATE
SELECTCOLUMNS (
    INFO.PARTITIONS (),
    "Table Name", [Name],
    "Query Definition", [QueryDefinition],
    "Source Category",
        IF (
            CONTAINSSTRING ( [QueryDefinition], "let Source" )
                || LEFT ( [QueryDefinition], 3 ) = "let",
            "M",
            "DAX"
        )
)

     

  • Jitmondo's avatar
    Jitmondo
    Helper III
    1 year ago

    Thank you for coming back to me Dane, tried something similiar but your post is better -  I suppose I could use infor .view for everything else except source and have this table - copy it out and into a sharpoint location with the rest of the data. It would be interesting to know if I could get the report name to populate in this table or link this up further some how.

    • danextian's avatar
      danextian
      Super User
      1 year ago

      Sorry. What do you mean by report name? DAX Query is local to the semantic model.

  • Jitmondo's avatar
    Jitmondo
    Helper III
    1 year ago

    Sorry - I mean when I combine this across multiple reports(planning to) I will need to differentiate them with identifier like report name, I suppose I could just manually insert each time but was looking for something to automate it completely. Right now we are using Dax Studio to populate a dictionary which covers source, tables,  columns, and measures - but is done in four files - I know there is a way to automate with power shell but not so great with it 🙂 Thanks again I will try the above in my solution.