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.

  • AlexisOlson's avatar
    AlexisOlson
    Super User
    5 years ago

    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.