Retrieve Columns and their descriptions from multiple semantic models

[PetyrBaelish]

I'm trying to create an organisation wide data dictionary based on a selection of Power BI reports. I want to use column names and column descriptions that have been populated in Power BI Desktop, and display them in a "data dictionary" report. I have successfully done this on a "per dataset" basis, using the following query, which uses parameters for WorkspaceName, DatasetName and ConnectionString (although the latter is set to "powerbi://api.powerbi.com/v1.0/myorg/" and doesn't change):

let
Query1 = AnalysisServices.Database(ConnectionString & WorkspaceName, DatasetName, [Query="select #(lf)#(tab)[ID],#(lf)#(tab)[TableID],#(lf)#(tab)[IsHidden],#(lf)#(tab)[DisplayFolder],#(lf)#(tab)[ExplicitName],#(lf)#(tab)[Description]#(lf)from #(lf)#(tab)$SYSTEM.TMSCHEMA_COLUMNS", Implementation="2.0"]),
#"Renamed Columns" = Table.RenameColumns(Query1,{{"DisplayFolder", "Display Folder"}, {"ExplicitName", "Column Name"}, {"Description", "Column Description"}})
in
#"Renamed Columns"

Note the above is for columns, althuogh I have two other queries for tables and measures, that just retrieve different columns.

At present, this means I have to recreate this report for every dataset I wish to have a data dictionary for.

Is it possible to do this just once, for all datasets I wish to include, in a single report. I'm thinking I could maintain a list (maybe in SharePoint, or just as manually entered data in Power BI), and for that list to contain WorkspaceName and DatasetName (my two parameters that change), and for my query above to then cycle through that list and retrieve the details for all datasets in the list, not just one?

I can't figure out how to do that.

[Anonymous · Accepted solution]

Hi @PetyrBaelish ,

Thanks for reaching out to our community.

According to my understanding, you are seeking a solution to compile data dictionaries for multiple datasets into a single report. 

To achieve this, you can try using List.Generate() to iterate through a list of datasets and compile the data dictionary for each. 

For example,

//Use List.Generate to iterate through the list of datasets and get column details 
ColumnDetailsList = List.Generate(
        () => [Index = 0, Result = GetColumnDetails(DatasetList{0}[WorkspaceName], DatasetList{0}[DatasetName])],
        each [Index] < List.Count(DatasetList),
        each [Index = [Index] + 1, Result = GetColumnDetails(DatasetList{[Index]}[WorkspaceName], DatasetList{[Index]}[DatasetName])],
        each [Result]
    ),

If you want to learn more about it, please refer to 

List.Generate() and Looping in PowerQuery - Exceed

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.