Help persisting dynamic query model data using the #shared query to applied data in report editor

[Anonymous]

Hi folks

I'm using the #shared object in power query to get a list of tables and columns in my query model. I want a dynamic list of columns and tables for use in a governance workflow. But I've found that it only instatiates these values within the query editor and they are not persisted to the report editor. When the query is applied and the table viewed in the report editor the values of any tables viewable in the query editor are not populated. It seems it's context senstive to the query editor in some way.

Sure I can copy and paste the values out of the dynamic list..... but this is not the dynamic approach I require.

I'm wondering if anyone has any ideas how to persist the values in a way that survives the query being applied to the model ?

For example using the code below

let
Source = #shared,
#"Converted to Table" = Record.ToTable(Source),
#"Added Custom" = Table.AddColumn(#"Converted to Table", "Column", each try Table.ColumnNames([Value]) otherwise null),
#"Expanded Column" = Table.ExpandListColumn(#"Added Custom", "Column"),
#"Filtered Rows" = Table.SelectRows(#"Expanded Column", each ([Column] <> null)),
#"Removed Duplicates" = Table.Distinct(#"Filtered Rows", {"Name"}),
#"Removed Columns" = Table.RemoveColumns(#"Removed Duplicates",{"Value", "Column"})
in
#"Removed Columns"

I've been trying to persist the values instantiated in query editor to another table in some way. e.g. custom column, convert to list, merge with another table..... but so far all these refresh to null values 

Hoping someone has a clever way.

Cheers

Simon

[v-juanli-msft]

Hi @Anonymous 

I'm not clear about your requirement.

What's purpose do you use the code?

Could you show me an example?

Best Regards
Maggie

[Anonymous]

Hi @v-juanli-msft 

The #shared  function gives dynamic access to the tables and their column metadata in the query editor. I wish to use this dynamic metadata as input to a data governance extract of the contents of the report. For example, dynamically exporting the metadata to data source landing page in the pbix report to assist a user to see what the report contains. But also exporting to a central repository of workspace report metadata to populate a data catalogue. I note that data catalogue features are an extremely poorly supported aspect of power bi currently and in need of much attention.

For example

using the example code 

let
Source = #shared,
#"Converted to Table" = Record.ToTable(Source),
#"Added Custom" = Table.AddColumn(#"Converted to Table", "Column", each try Table.ColumnNames([Value]) otherwise null),
#"Expanded Column" = Table.ExpandListColumn(#"Added Custom", "Column"),
#"Filtered Rows" = Table.SelectRows(#"Expanded Column", each ([Column] <> null)),
#"Removed Duplicates" = Table.Distinct(#"Filtered Rows", {"Name"}),
#"Removed Columns" = Table.RemoveColumns(#"Removed Duplicates",{"Value", "Column"})
in
#"Removed Columns"

One can obtain a set of metadata in the query editor about the data sources as shown....

I have not yet tried but I will test if this can be exported to an external file such as json, csv with something like R.

But in the first instance I want this metadata to be accessible as a data source(table) in the report designer. For example to populate a landing page in the report for a user to explore the contents of the report pbix.

When the above queries are applied the resulting table from the #shared functgion query in the report designer contain no content.

So I'm seeking a method to persist the data available in the query editor to the report desinger tables.

Many thanks in advance

Simon

[v-juanli-msft · Accepted solution]

Hi @Anonymous 

I'm afraid it is impossible to make the "Query1" work in Report view as it does in Power Query.

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @v-juanli-msft 

Thanks for the reply.

I trust that you understood the goal of my use case to extract metadata from the report. Since the shared function does not persist in the manner discussed can you suggest another method to create a dynamic metadata extract of all the tables and ideally their columns within a report.

I do know obout offline solutions such as DAX editor but I want the metadata to be auto generated within the pbix report.

Any ideas appreciated

Regards

Simon

[v-juanli-msft]

Hi @Anonymous 

Sorry, i still can't find any workarounds.

I see many customers request for this requirements, but it seems impossible in current version of Power BI.

https://community.powerbi.com/t5/Power-Query/Getting-a-list-of-all-columns-in-all-tables-in-the-data-model/m-p/780255

Maybe you can submit an idea in Power BI Idea forums.

If you want to change the data source or table shown on report view, you may try "query parameter".

https://biinsight.com/power-bi-desktop-query-parameters-part2-dynamic-data-masking-and-query-parameters/

Power BI Desktop Query Parameters, Part 1

Best Regards
Maggie