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 am looking to get all the names of the columns of all the tables in a data model. No data, just the column names in a simple table like this:
Table ColumnName
------- ---------- Table1 id Table1 CustCode Table1 Amount Table2 id ..... .....
I created a list of all the tables TableList but not sure how to iterate that list when column names are unknown?
Thanks for your hints.
Solved! Go to Solution.
you could use #shared for this, but then you have to manually filter the queries
let Source = #shared, #"Converted to Table" = Record.ToTable(Source), #"Filtered Rows" = Table.SelectRows(#"Converted to Table", each ([Name] = "Query")), #"Added Custom" = Table.AddColumn(#"Filtered Rows", "Column", each Table.ColumnNames([Value])), #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Value"}), #"Expanded Column" = Table.ExpandListColumn(#"Removed Columns", "Column") in #"Expanded Column"
you could use #shared for this, but then you have to manually filter the queries
let Source = #shared, #"Converted to Table" = Record.ToTable(Source), #"Filtered Rows" = Table.SelectRows(#"Converted to Table", each ([Name] = "Query")), #"Added Custom" = Table.AddColumn(#"Filtered Rows", "Column", each Table.ColumnNames([Value])), #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Value"}), #"Expanded Column" = Table.ExpandListColumn(#"Removed Columns", "Column") in #"Expanded Column"
@Stachu , this doesn't work for me. There are no values "Query" in the [Name] column.
OK, never seen this #shared before. Can you explain a little how this script works?
Hi @gvg,
#shared is a record, which contains all tables (queries) and functions. It is a mixture of the PBI core functionality and your custom objects. Check the screenshot below.
Please be carefull with #shared. It isn't supported for refreshes in Power BI Service. It works only in Power BI Desktop. More about the problem: https://community.powerbi.com/t5/Power-Query/Expression-Evaluate-shared-in-PowerBI-Service/td-p/7316...
And to an untold question, which would come as next for sure: Unfortunately, there isn't another way how to get all columns of all tables.
I'm using the #shared object to get a list of tables and columns in my model. I wanted a dynamic list of columns and tables for a governance workflow. But I've found that it only instatiates these values within the query editor. When the query is applied and the table viewed in the report editor the values of any tables in the query editor are not populated.
Sure I can copy and paste the values..... 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.
Try this one:
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 Columns" = Table.RemoveColumns(#"Filtered Rows",{"Value"})
in
#"Removed Columns"
Where do I put this code? I'd like to create a new table showing all the tables and columns associated with this .pbix file. Any leads?
Thank you.
I was able to figure it out:
- Click "Add data" to create a new table
- Enter "test" into one column
- Click "Transform data"
- Paste the code from @Anonymous into the "Advanced Editor"
How do we get the view to persist?
In the query editor for the new table, all TableNames and ColumnNames are shown...
But as soon as I `Close and Apply`, none of the values are visible...?
I also have a similar requirement, any got how to make these values persistent?
Did you figure this out? Getting same thing
Hello friend! Didi you solved this problem? Chears
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.
User | Count |
---|---|
102 | |
48 | |
19 | |
13 | |
11 |