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

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.