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

Hi all, 

I've found a very good video which provides the solution to create a meta PowerBI Report: (160) Using the Scanner API in Power BI - Chicago & MKE Power BI User Group - YouTube

Hi PowerSchmiede ,

I tried to fix your issue with Power Query, but did not get very far. This is all the meta data I could get out:

So this is probably not sufficient... I did some research and came across these ones here:

Export a list of data source TABLES from a dataset - Microsoft Power BI Community

Using model's metadata to create table - Microsoft Power BI Community

They suggest to go via DAX studio where you should get some more stuff. However, it seems like you still would need to manually export the information into a txt file and take it from there. So not fully automated either. 

Then I thought - since you wanna save the information into sharepoint - you might wanna shoot some APIs to PBI to fetch what you need. These ones here should get you pretty far, I hope:

Datasets - Get Datasets - REST API (Power BI Power BI REST APIs) | Microsoft Docs

Datasets - Get Datasources - REST API (Power BI Power BI REST APIs) | Microsoft Docs

Push Datasets - Datasets GetTables - REST API (Power BI Power BI REST APIs) | Microsoft Docs

Note, here you need quite some permissions, to be able to retrieve those information. 

Though maybe not exactly what you expected, I still hope this helps a bit! 🙂

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

Hi tackytechtom,

thx for your answer and the many suggestions to find a solution. 

I was hoping there was an easier solution with query m. 

I did some researche for the Power BI API and I think this could be possible. I saw this video from a Microsoft You Tube Channel and in combination with this Link to embed a service principal it could be done. But it is a long way. 

If someone has an easier way, please share with us. 

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/