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.   

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.