cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
simo-climb Frequent Visitor
Frequent Visitor

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

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

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Help persisting dynamic query model data using the #shared query to applied data in report edito

Hi @simo-climb 

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.

View solution in original post

5 REPLIES 5
Community Support Team
Community Support Team

Re: Help persisting dynamic query model data using the #shared query to applied data in report edito

Hi @simo-climb 

I'm not clear about your requirement.

What's purpose do you use the code?

Could you show me an example?

 

Best Regards
Maggie

simo-climb Frequent Visitor
Frequent Visitor

Re: Help persisting dynamic query model data using the #shared query to applied data in report edito

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

 

ex_metadata1.PNG

 

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.

 

ex_reportEditorView.PNG

 

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

 

 

 

 

Community Support Team
Community Support Team

Re: Help persisting dynamic query model data using the #shared query to applied data in report edito

Hi @simo-climb 

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.

View solution in original post

simo-climb Frequent Visitor
Frequent Visitor

Re: Help persisting dynamic query model data using the #shared query to applied data in report edito

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

 

 

Community Support Team
Community Support Team

Re: Help persisting dynamic query model data using the #shared query to applied data in report edito

Hi @simo-climb 

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

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

Power BI Desktop Query Parameters, Part 1

 

Best Regards
Maggie

 

Helpful resources

Announcements
New Kudos Received Badges Coming

New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Solution Authors
Top Kudoed Authors (Last 30 Days)
Users online (1,559)