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).

 

Query 2 is a table with a single column only - so a VLOOKUP equivalent won't work.

 

Thanks for any assistance. 

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).

14 Replies

  • edhans's avatar
    edhans
    Community Champion
    6 years ago

    Add a custom column in Query 1 to just say =Query2, or =#"Query 2" if it has a space in it.

     

    Then expand that one column you want.

     

    Alternatively, if you don't need Query 2 to be a table, right-click on the single value in Query 2, and select drilldown. Power Query will turn that table into a value.

     

    Then in Query 1, add a custom column that just says =Query2, or =#"Query 2" and just that value will be there.

    • MC4's avatar
      MC4
      Frequent Visitor
      6 years ago

      Thanks edhans for responding!

       

      I have attempted your suggestion - however the custom column has populated with "Table". I'm not sure if this is because Query 2 has multiple rows?

       

      With that - I would like to be able to take a value from a specific row in Query 2 to populate the Custom Column in Query 1 - say the value from the 3rd row in Query 2. Is this possible?

       

      Thanks again.

      • v-frfei-msft's avatar
        v-frfei-msft
        Community Support
        6 years ago

        Hi MC4 ,

         

        Please tyr to merge table. If it doesn't meet your requirement,  kindly share your sample data and excepted result to me if you don't have any Confidential Information. Please upload your files to One Drive for Business and share the link here.

         

  • Anonymous's avatar
    Anonymous
    Not applicable
    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).
    • MC4's avatar
      MC4
      Frequent Visitor
      6 years ago

      Thank you Anonymous ! Exactly what I've been trying to achieve. Such a simple solution in the end.

       

      Thanks to all who assisted with the question also. Much appreciated. 

       

      Cheers!