Forum Discussion

PowerSchmiede's avatar
PowerSchmiede
Frequent Visitor
4 years ago

Create Metadata Table for Table Name, Source Item and Dataset Name

I try to create a dynamic Table which contain the following data:   1. All Table names from the Dataset (optional)   2. All Source Items    3. The Name of the Dataset   The result...
Need Help
tackytechtom's avatar
tackytechtom
Most Valuable Professional
3 years ago

Hi Anonymous ,

 

If you refer to the image I posted, then the below is the M code I used. I was Lucky enough to have saved that query in a pbix file 😄 

let
    MetaQuery = #sections,
    Section1 = MetaQuery[Section1],
    #"Converted to Table" = Record.ToTable(Section1),
    #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Name", "Table Name"}}),
    #"Added Custom" = Table.AddColumn(#"Renamed Columns", "Custom", each Table.RowCount ([Value])),
    #"Renamed Columns1" = Table.RenameColumns(#"Added Custom",{{"Custom", "Number of Rows"}}),
    #"Added Custom1" = Table.AddColumn(#"Renamed Columns1", "Custom", each Table.ColumnCount([Value])),
    #"Renamed Columns2" = Table.RenameColumns(#"Added Custom1",{{"Custom", "Number of Columns"}}),
    #"Removed Columns" = Table.RemoveColumns(#"Renamed Columns2",{"Value"})
in
    #"Removed Columns"

 

However, you might wanna consider using DMVs where this information should be available as well. Here a blog post to find out more:

https://www.tackytech.blog/how-to-build-meta-reports-on-top-of-power-bi-datasets-with-dynamic-management-views-dmvs/

 

Let me know, if this helps 🙂

 

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/