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

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

    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.