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

[gvg]

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.

[Stachu · Accepted 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"

[gvg]

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

[Stachu]

"Query" was a name of a table that was in my model, you need to filter for whatever are your query names

[gvg]

OK, thank you @Stachu !

[gvg]

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

[Nolock]

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.

[Anonymous]

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.

[Anonymous]

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"

[ericOnline]

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.

[ericOnline]

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

[Anonymous]

I also have a similar requirement, any got how to make these values persistent?

[aaronmtz93]

Did you figure this out? Getting same thing

[Joao_Macau]

Hello friend! Didi you solved this problem? Chears

[gvg]

Thank you @Nolock !