cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
gvg Member
Member

Getting a list of all columns in all tables in the data model

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.

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Stachu Super Contributor
Super Contributor

Re: Getting a list of all columns in all tables in the data model

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"
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

View solution in original post

8 REPLIES 8
Stachu Super Contributor
Super Contributor

Re: Getting a list of all columns in all tables in the data model

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"
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

View solution in original post

gvg Member
Member

Re: Getting a list of all columns in all tables in the data model

OK, never seen this #shared before. Can you explain a little how this script works?

Highlighted
Super User
Super User

Re: Getting a list of all columns in all tables in the data model

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.

 

Capture.PNG

gvg Member
Member

Re: Getting a list of all columns in all tables in the data model

@Stachu , this doesn't work for me. There are no values "Query" in the [Name] column.

Stachu Super Contributor
Super Contributor

Re: Getting a list of all columns in all tables in the data model

"Query" was a name of a table that was in my model, you need to filter for whatever are your query names
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

gvg Member
Member

Re: Getting a list of all columns in all tables in the data model

OK, thank you @Stachu !

gvg Member
Member

Re: Getting a list of all columns in all tables in the data model

Thank you @Nolock !

simo-climb Frequent Visitor
Frequent Visitor

Re: Getting a list of all columns in all tables in the data model

Hi @Nolock  and @Stachu 

 

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.

 

Helpful resources

Announcements
Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Top Kudoed Authors
Users Online
Currently online: 179 members 1,777 guests
Please welcome our newest community members: