Error when applying a query to Power BI (cannot convert the value null to type Table)

[pruvieros]

Hello,

I'm trying to export  all tables names and all column names from Power Query to a csv file

I found the very powerful keyword #shared in the radacad website, that allows to get a list of all Tables and Functions in Power Query.
https://radacad.com/power-query-library-of-functions-shared-keyword

Then this list can be transformed into a table by clicking on the button "To table"

Then, it is easy to add to this table a custom column that will include all columnn names of each table.

The last step is to expand this custom column ("Expand to new rows")

Everything works fine as long as I am in Power Query

But when I want to Apply this query in order to get back to Power Bi, I get the message:
"Error OLE DB or ODBC: [Expression.Error] We cannot convert the value null to type Table"

However, I have no null value in my table!!!!

Do you have an idea?

In order to reproduce  the problem, you can use the following procedure

1- Create a csv file named "sample.csv" with the following information:

Product;Price
Apple;10
Banana;20

2- Import it in Power Query

3- Add a blank Query with the keyword #shared (in the advanced editor, delete all other keywords), then click OK

4- Transform it to a table with the button To Table. You should get a table with 2 columns named "Name" and "Value"

5- Filter the lines so that the only one remaining is the one with the name of your table (the table imported from the csv file)

6- Add a custom column with the instruction =Table.ColumnNames([Value])

(In this step, I assume that the name of the second column is "Value")

7- Expand this custom column by clicking on the button in the header.

8- Try to "Close and apply".

Do you get an error message?

[pruvieros]

Hello lbendlin,

Thanks for your reply and for your example file.

I am currently using the Pro version of Power BI, so I don't have access to DMV queries.

In the process I described above, the purpose of the keyword #shared is to automatically get ALL table names of my pbix file (and then to automatically apply on those tables the function Table.Names)

I've presented an example as simple as I could, but in fact,  I'm using pbix files with many tables, each of them containing many columns, and I'm looking for a solution to automatically get the list of ALL tables with ALL columns of those tables (if possible from within Power Query).

It seems to me that the  keyword #shared is an easy way to get the names of all tables, but I am very interested if you know a better way to do it!

Thanks in advance

Pruvieros

[lbendlin · Accepted solution]

You don't need a license to run DMV queries against the PBIX file  (for example with DAX Studio). Only requirement is that the PBIX is opened with Power BI Desktop.

[lbendlin]

You don't really need to use #shared.  Easier to use Table.ColumnNames() or other meta functions.  Or use DMV queries.