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

  • Question

  • I have appended several tables together and would like to add a custom column that lists each of the table names that the data came from.  I've tried following other threads asking the same thing but I'm not able to reproduce the same results on my query; M syntax is beyond my threshold of knowledge.

    The advanced editor currently looks like this:

    let
        Source = Table.Combine({associate1, associate2, associate3, associate4, associate5, associate6, associate7, associate8, associate9, associate10, associate11, associate12, associate14, associate15, associate13}),
        #"Removed Blank Rows" = Table.SelectRows(Source, each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null})))
    in
        #"Removed Blank Rows"

    Friday, October 5, 2018 12:24 PM

Answers

  • I don't know of any "proper" way to do this. What I do know you can do is create a query that contains every query inside it with the Names of every query as a field. Then you can filter that list to the tables you want to combine and expand them, and it will retain the name field so you know what table the data came from.

    So in this example you would create a blank query and called it "CombinedTables" and paste this code into the advanced editor. Here it only filters itself out of the list (because a list of all queries will include this query as well) and then you would just expand the Value column to combine the tables. You would want to alter the filtering to make sure it is only including the tables you want to combine.

    let Source = #table({"TableNames"}, {{Record.ToTable(#sections[Section1])}}), #"Expanded TableNames" = Table.ExpandTableColumn(Source, "TableNames", {"Name", "Value"}, {"Name", "Value"}), #"Filtered Rows" = Table.SelectRows(#"Expanded TableNames", each ([Name] <> "CombinedTables")) in #"Filtered Rows"



    Friday, October 5, 2018 4:03 PM
  • A possible way to go is a formula like this:

    Table.FromColumns({
    {"associate1", "associate2", ...}, 
    {associate1, associate2, ...}
    })

    It creates a table with 2 columns: first column has the column names (therefore you write them in "") and the second column has the tables itself. All you have to do then is to expand the columns from the tables. So don't use the Table.Combine formula here, but the fomula above.


    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!

    Saturday, October 6, 2018 3:03 PM
    Moderator

All replies

  • I don't know of any "proper" way to do this. What I do know you can do is create a query that contains every query inside it with the Names of every query as a field. Then you can filter that list to the tables you want to combine and expand them, and it will retain the name field so you know what table the data came from.

    So in this example you would create a blank query and called it "CombinedTables" and paste this code into the advanced editor. Here it only filters itself out of the list (because a list of all queries will include this query as well) and then you would just expand the Value column to combine the tables. You would want to alter the filtering to make sure it is only including the tables you want to combine.

    let Source = #table({"TableNames"}, {{Record.ToTable(#sections[Section1])}}), #"Expanded TableNames" = Table.ExpandTableColumn(Source, "TableNames", {"Name", "Value"}, {"Name", "Value"}), #"Filtered Rows" = Table.SelectRows(#"Expanded TableNames", each ([Name] <> "CombinedTables")) in #"Filtered Rows"



    Friday, October 5, 2018 4:03 PM
  • Do you need the table names to be exact, or do you just need them to be different? (For example, would just having an index number column, where each source table had a different index, be sufficient?)

    Ehren

    Friday, October 5, 2018 5:46 PM
    Owner
  • A possible way to go is a formula like this:

    Table.FromColumns({
    {"associate1", "associate2", ...}, 
    {associate1, associate2, ...}
    })

    It creates a table with 2 columns: first column has the column names (therefore you write them in "") and the second column has the tables itself. All you have to do then is to expand the columns from the tables. So don't use the Table.Combine formula here, but the fomula above.


    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!

    Saturday, October 6, 2018 3:03 PM
    Moderator
  • This did the trick, thank you for your help!!!
    Friday, November 2, 2018 6:56 PM