Data dictionary

[Oded-Dror]

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

[hnguy71 · Accepted solution]

@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!

[rohit1991]

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]

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 · Accepted solution]

@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!

[Oded-Dror · Accepted solution]

Thanks,

It works