none
Is there a way to get a table name in Power Query? RRS feed

  • Question

  • I want to append several tables but want the end result to also show the table name the data came from. I know I can go into each table and add a custom column and assign it a name with ="TableName" as the formula, but that is manual.

    I'd like to just have the name that is in the SOURCE command. PQ knows the name of the table, but I cannot figure out if there is a way to get PQ to divulge that info.


    Ed

    Friday, March 3, 2017 4:30 PM

Answers

  • Not really sure about what you're after, but this might be a start: It "appends" the tables from 2 queries in your current workbook (called "Budget" and "Actuals") and shows their query/source-name in the first column:

    let
        Source = #table({"Tables"}, {{"Actuals"}, {"Budget"}}),
        Evaluate = Table.AddColumn(Source, "Custom", each Expression.Evaluate([Tables], #shared)),
        Expand = Table.ExpandTableColumn(Evaluate, "Custom", List.Union(List.Transform(Evaluate[Custom], each Table.ColumnNames(_))))
    in
        Expand

    You can replace the expression in the "Source"-step with a reference to an Excel-table for example, that holds all the names of the tables to be appended. The name of the column that holds the names must be "Tables".


    Imke Feldmann TheBIccountant.com

    • Marked as answer by EdHans Friday, March 3, 2017 7:16 PM
    Friday, March 3, 2017 7:03 PM
    Moderator

All replies

  • Not really sure about what you're after, but this might be a start: It "appends" the tables from 2 queries in your current workbook (called "Budget" and "Actuals") and shows their query/source-name in the first column:

    let
        Source = #table({"Tables"}, {{"Actuals"}, {"Budget"}}),
        Evaluate = Table.AddColumn(Source, "Custom", each Expression.Evaluate([Tables], #shared)),
        Expand = Table.ExpandTableColumn(Evaluate, "Custom", List.Union(List.Transform(Evaluate[Custom], each Table.ColumnNames(_))))
    in
        Expand

    You can replace the expression in the "Source"-step with a reference to an Excel-table for example, that holds all the names of the tables to be appended. The name of the column that holds the names must be "Tables".


    Imke Feldmann TheBIccountant.com

    • Marked as answer by EdHans Friday, March 3, 2017 7:16 PM
    Friday, March 3, 2017 7:03 PM
    Moderator
  • Nailed it. I was trying to do something after a Table.Combine statement. Didn't even consider doing what you did. Thanks!

    Ed

    Friday, March 3, 2017 7:17 PM
  • Hello

    Very interresting.

    I chose the last proposition : You can replace the expression in the "Source"-step with a reference to an Excel-table for example, that holds all the names of the tables to be appended. The name of the column that holds the names must be "Tables"

    But... With Excel 2010 and the last PowerQuery update when I create it in PowerQuery everything is OK but when I try to use the Connection in Excel I have an error about the Expression (first Table not find in this context).

    If I open with 2016, the file I created in 2010, I can use the connection.

    If I save the file in 2016 then open it in 2010, I can use the connection...

    Any idea about this ?

    Thanks

    Chris



    • Edited by 78chris Friday, July 13, 2018 1:30 PM
    Friday, July 13, 2018 1:30 PM
  • "You can replace the expression in the "Source"-step with a reference to an Excel-table for example, that holds all the names of the tables to be appended. The name of the column that holds the names must be "Tables"."

    Some help for a novice please, what would the M Code for the above look like?

    Thanks

    Chris

    Monday, November 5, 2018 11:48 AM
  • Hope this video explains it: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/Power-BI-Forum-Help-How-to-integrate-M-code-into-your-existing/m-p/179314

    If not, please let me know.


    Imke Feldmann - MVP Data Platform (PowerBI) - http://www.TheBIccountant.com 

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful - Thanks!

    Monday, November 5, 2018 12:13 PM
    Moderator
  • Imke,

    I found your method very useful, but did discover a slight additional requirement in order to get the process to work where Source was set to a Table containing the Table Names. I was getting the Error below:

    "Formula.Firewall: Query 'Your Example' (step 'Expand') references other queries or steps, so it may not directly access a data source. Please rebuild this data combination."

    To get around this in Power Query, I had to change the Query Options / Current Workbook / Privacy Levels setting to "Ignore the Privacy..."

    Thanks for the excellent code, it saved me a ton of head scratching.

    Michael 

    Friday, July 10, 2020 8:56 PM