Forum Discussion

dd88's avatar
dd88
Post Patron
3 years ago

Power Bi – query or formula to Get Table name

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. F...
Need Help
dd88's avatar
dd88
Post Patron
3 years ago

Thanks ImkeF 

jbwtp's avatar
jbwtp
Memorable Member
3 years ago

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's avatar
    dd88
    Post Patron
    3 years ago

    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's avatar
      jbwtp
      Memorable Member
      3 years ago

      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