Power Bi – query or formula to Get Table name

[dd88]

I have 6 tables.

In each table, I would like to add a new column.  with a formula to get the current table.Name,  and add the value in the new column.

In Power Bi, the table has properties. For example I would have thought the following value could be obtained -

strTableName = Table.Properties.Name

and the value is populated in the new column

I have done a search, and  thought this was a simple task/ query.

TIA

[v-stephen-msft]

Hi @dd88 ,

When you connected to an excel file successfully, you can select a table and click on the source step, which will have the Sheet's name column. 

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ImkeF]

Hi @dd88 
that's not possible, unfortunately. 
The only workaround I see is to use the import from folder method for your queries. Then you would be able to use the original file name for it automatically.

[dd88]

Thanks @ImkeF 

[jbwtp]

Hi @dd88,

I suspect that you and Imke may have in mind different "tables". Her suggestions (as it appears to me) relates to file names being derived and then assigned as strTableName. Which is, of course, correct, but it looks like what you want/talk about in your question is the actual query name (aka table name), which you assign to the query in PBI. Is this right?

If yes, I do not think that there is a way to solve it using a function in PBI. Queries are unaware of their own name (or any other query name in the PBI project) in the sense that is supported in the Excel object model, for example. There is a global enumerator for the namespace in PBI - #shared - but there is no way for the query to tell which of the global objects in the enumerator is which.

However, as the name is "hard-coded" you can just add a column with a fixed value. If you are planning to use the query as a template, you can make the first step a parameter containing the tableName value (this way, you can quickly find/replace it in the code.

Cheers,

John

[dd88]

Thanks @jbwtp

One of the above suggestions is using  import from folder method for your queries.  This is if there are several Excel files.

My data source is 1 Excel spreadsheet, with 6 different tabs. Each tab has the same columns, each tab is a different department.

Workarounds can be either:
in the Excel Spreadsheet add a new Column for 'Department'. A formula to obtain the tab Name which is the Deparment Name.

or, as you suggested ..

in PowerBI, Add a new Column 'Department' with a fixed value that is the department name.

In .NET code, intellisense provides property data information (for most objects properties).  I thought the same would be available in PowerBI Query.

Many thanks

[jbwtp]

Hi @dd88,

This is a bit different to the functionality available in M.

Excel, actually, exposes the internal file structure through the connector, and you should be able to see all tab names on one of the importing steps, I think this is Excel.Workbook(). You can use the list of tabs returned on this step to assign the value for your column dynamically.

Cheers,

John