Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
Hi @Anonymous
I'm afraid it is impossible to make the "Query1" work in Report view as it does in Power Query.
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
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
Hi @Anonymous
I'm afraid it is impossible to make the "Query1" work in Report view as it does in Power Query.
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
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.
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".
Power BI Desktop Query Parameters, Part 1
Best Regards
Maggie
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.