Forum Discussion

DJPrometheus's avatar
DJPrometheus
Microsoft Employee
5 years ago
Solved

Reflexive Table Reference

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?
Need Help
  • AlexisOlson's avatar
    AlexisOlson
    5 years ago

    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's avatar
DJPrometheus
Microsoft Employee
5 years ago

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.   

DJPrometheus's avatar
DJPrometheus
Microsoft Employee
5 years ago

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's avatar
    AlexisOlson
    Super User
    5 years ago

    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.