Forum Discussion

MC4's avatar
MC4
Frequent Visitor
6 years ago
Solved

Custom Column - Value from other query

Hi there,   I have added a Custom column to a Quaery (Query 1) in Power Query and I want the value on each row in this column to equal to the value from a single row in another Query (Query 2).   ...
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago
    Just do this:
    Table.AddColumn(PrevStep, “NewColumnName”, each OtherTable[ColumnName]{2})
    //{2} being the zero-based position of the other table’s item that you want. You could also do
    each OtherTable[ColumnName] = “Some Team”, type text).
edhans's avatar
edhans
Community Champion
6 years ago

See the attached PBIX file here. 

I'm not clear on the purpose here. Your table already had the club name so not sure why you wanted to get the club name merged from the other table.

 

If the goal is to get the club name there in the first place, you need some way to link the two tables. In other words, how should a computer know that Club 3 is what goes in your column? You've given it no other info to make that determination.

 

If I'm misunderstanding, please let me know. If the merge example is what you needed, please mark this as a solution.

artemus's avatar
artemus
Microsoft Employee
6 years ago

Assuming you want to glue the other table vertically you can try this (Add custom step):

 

= #table(type table Type.ForRecord(Type.RecordFields(Type.TableRow(Value.Type(PreviousStep))) & Type.RecordFields(Type.TableRow(Value.Type(OtherTable))), false), List.Transform(List.Zip({Table.ToRows(PreviousStep), Table.ToRows(OtherTable)}), each _{0} & _{1}))

 

Replace PreviousStep with the previous step in your query, and OtherTable with your other table. 

  • MC4's avatar
    MC4
    Frequent Visitor
    6 years ago

    Thanks artemus  - This feels close to what I am trying to do.

     

    I have added your suggested custom step as a formula to my custom column. However, when I do this, the "Club Name" column in Table 2 is populated with "Table" and not the desired "Club 3".

     

    This may be a result of how I have translated the Custom Step you provided.

     

    Here is a link to the file in which perhaps you can see how I have attempted to transalte your custom step

     

    https://www.dropbox.com/s/6qlfvrdw5h8cwz6/Sample%20Power%20BI%20R1.pbix?dl=0 

     

    Thanks!