Reflexive Table Reference

[DJPrometheus]

Can a table in a Power Query/M reference itself?  E.g. populate a column with the name of the current table through Add Custom Column?

[DJPrometheus]

That worked perfectly - I'll accept it as a solution, but I am curious how the table combination part works?  Is there a good blog article on this?

[AlexisOlson]

I don't know of any related blog articles but it's just an application of the Table.FromColumns function (combined with a few years of experience with M).

Here's the documentation for that function:

https://docs.microsoft.com/en-us/powerquery-m/table-fromcolumns

[DJPrometheus]

I'm still a bit confused - are you saying let the queries populate and then run this in DAX against the model?  I was trying to do the transform as part of the initial querying of the data source (web).

[AlexisOlson · Accepted solution]

No DAX is involved at all. This is the Power Query language M.

The code I gave is what you would use in the Advanced Editor to define your combined query. The whole thing would look something like this:

let
    Source = Table.FromColumns({{#"BMX Bikes", #"Mountain Bikes", #"Hybrid Bikes"}, {"BMX Bikes", "Mountain Bikes", "Hybrid Bikes"}}, {"Table", "ItemType"}),
    #"Expanded Table" = Table.ExpandTableColumn(Source, "Table", {"Serial", "Status", "Shipped"}, {"Serial", "Status", "Shipped"})
in
    #"Expanded Table"

[DJPrometheus]

I think I see what you mean - right click on the left nav bar (Queries) and create a new blank query.  Then use this to combine everything + the extra column with the table names.  Testing it momentarily.

[DJPrometheus]

Pulling items tables from web:

Table is the ItemType

ItemSerialNumber, ItemStatus, DateTimeShipped

I will be appending multiple of these with the same table structure, so after all of my cleansing operations in the query, I would like to add a custom column "ItemType" that populates with a reference to the table name.

Then I can copy-paste this line into advanced editor for the 10 other tables with the same structure.  Then I would append queries as new table "Combined Results" and I could track the ItemType/ItemFamily.

Sure, it's not really relational, but I don't really care to make it complex for something this simple.   

[AlexisOlson]

So the goal is to apply the same cleansing operations to each query?

Writing a table function would be a quick and clean way to do that without having to duplicate code and would allow any updates to the cleaning function to automatically apply to all the tables you use it on instead of having to update each one separately.

[DJPrometheus]

Sorry, I should clarify my language a bit - this would a Custom Column spec in a query referencing the query name itself.  E.g. Table is BMX Bikes; ItemSerials, ItemStatus, DateTimeShipped.

If I am pulling BMX Bikes, Mountain Bikes, Hybrid Bikes, etc. and they all have the same query structure, then for reporting, I would prefer to populate a new custom column with the query name, combine all of them together, and then display in a report.

Sure I could make an ID table after import with each type and then join it, but it seems much simpler to add custom column, append queries, and call it a day.

[AlexisOlson]

Ah, I think I get it better now. I didn't see this when I wrote my previous reply.

Here's an alternative approach that isn't too bad:

Table.FromColumns(
    {
        {#"BMX Bikes", #"Mountain Bikes", #"Hybrid Bikes"},
        {"BMX Bikes", "Mountain Bikes", "Hybrid Bikes"}
    },
    {"Table", "ItemType"}
)

Here you've got a list of tables alongside the list of table names combined into a table with two columns, Table and ItemType.

Expand the Table column and you should have the combination you're after.

[AlexisOlson]

Even if it's possible, it seems like it might not necessarily be a good idea. Generally, you don't want the result of the query to change if you rename it.

I can't think of a way to do this without self-reference errors but maybe there's an alternative for your ultimate goal. What is the motivation for wanting to do this?