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

[PowerSchmiede]

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 should look like this:

I do not need really need the table in Power BI Desktop. I will export this Table with Power Automate to a SharePoint List which list all Dataset Metadata. So if there is a solution in Power Automate it will be also fine. 

Hope you can help me. 

Best Regards

Marcus 

[tackytechtom]

Hi @jeremyir ,

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-manage...

Let me know, if this helps 🙂

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

[PowerSchmiede]

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

[PowerSchmiede]

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. 

[tackytechtom]

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/

[jeremyir]

How did configure the query in your image? I need exactly that for some analysis I'm trying to do.