Forum Discussion

Oded-Dror's avatar
Oded-Dror
Helper III
1 year ago
Solved

Data dictionary

Hi there,


I was able to get the table name original and rename 

let
Source = Sql.Database("localhost", "AdventureWorksDW2022"),
dbo_DimProduct = Source{[Schema="dbo",Item="DimProduct"]}[Data]
in
dbo_DimProduct

Using the following Dax

// Finalizig the report with Original tables name
EVALUATE
VAR _table =
 FILTER(
 SELECTCOLUMNS(
INFO.VIEW.TABLES(),
"IsHidden",[IsHidden],
"Original Table Name", LEFT(SUBSTITUTE([TableStorage]," ","-"),SEARCH("-",SUBSTITUTE([TableStorage]," ","-"))-1),
"Modified Name",[name]
 ),
  [IsHidden] = FALSE()
  )
Return
SUMMARIZE(
    _table,
    [Original Table Name],
    [Modified Name]
)
 
I rename from DimProduct to Product and got the expected results
 Now
My quetion is there any whay to get the source and Schema like the table name I just did?  
Iv'e tried  INFO.DATASOURCES() but it return empty results.
I need the following:
Source = Sql.Database
Schema="dbo"
Or for Excel
Source = Excel.Workbook
Item="Table1" 

Thanks,
Oded Dror
Need Help
  • hnguy71's avatar
    hnguy71
    1 year ago

    Oded-Dror 

    At the moment, there's no direct way of generating this in DAX, but there are some workarounds...

     1. You can directly query your model using the DAX Query View. You can then check for it in the "QueryDefinition" field.

     

    2. You can use a 3rd party application to retrieve that information such as DAX Studio and query the DMV and selecting the schema partitions:

     

    3. Third method, if your model is saved on Power BI service, you can query the same model to return you all your query definitions:

     

    AnalysisServices.Database("powerbi://api.powerbi.com/YOUR_WORKSPACE_ID" "YOUR_DATASET_NAME", [Query= "select * from $SYSTEM.TMSCHEMA_PARTITIONS"])

     

    Hope that helps!

4 Replies

  • rohit1991's avatar
    rohit1991
    Super User
    1 year ago

    Hi, To extract Source and Schema details in Power Query:

    1. Open Advanced Editor:

      a.Inspect the M code, e.g.,

     

    let
    Source = Sql.Database("localhost", "AdventureWorksDW2022"),
    dbo_DimProduct = Source{[Schema="dbo", Item="DimProduct"]}[Data]
in
    dbo_DimProduct
​

     

            b. Source: Sql.Database, Schema: "dbo".

     

         2. For Excel:

     

    let
    Source = Excel.Workbook(File.Contents("C:\Data\Example.xlsx"), null, true),
    Table1 = Source{[Item="Table1", Kind="Table"]}[Data]
in
    Table1
​

     

             a.Source: Excel.Workbook, Item: "Table1".

     

         3.List All Sources and Schemas: Use the following query:

     

    let
    AllTables = Excel.CurrentWorkbook(),
    Metadata = Table.AddColumn(AllTables, "SourceDetails", each try Value.Metadata([Content]) otherwise null)
in
    Metadata
​

     

    Note: DAX cannot retrieve this metadata directly. Use Power Query or external tools like DAX Studio instead

     

    • Oded-Dror's avatar
      Oded-Dror
      Helper III
      1 year ago

      Look like there is a miss understanding , I don't want to open PQ advanced editor I wanted to use
      DAX Info function like I wrote in my question

      For example evaluate info.view.tables will give you tables name.

      My question is is there a DAX funcrion to retrieve the source and Schema?

      • hnguy71's avatar
        hnguy71
        Super User
        1 year ago

        Oded-Dror 

        At the moment, there's no direct way of generating this in DAX, but there are some workarounds...

         1. You can directly query your model using the DAX Query View. You can then check for it in the "QueryDefinition" field.

         

        2. You can use a 3rd party application to retrieve that information such as DAX Studio and query the DMV and selecting the schema partitions:

         

        3. Third method, if your model is saved on Power BI service, you can query the same model to return you all your query definitions:

         

        AnalysisServices.Database("powerbi://api.powerbi.com/YOUR_WORKSPACE_ID" "YOUR_DATASET_NAME", [Query= "select * from $SYSTEM.TMSCHEMA_PARTITIONS"])

         

        Hope that helps!