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

  • MC4's avatar
    MC4
    Frequent Visitor
    6 years ago

    Apologies.

     

    Here is a link to a sample file on Dropbox; 

     

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

     

    In Table 2 there is a Custom Column called "Club Name" which is currently populated with just text ("Club 3").

     

    I would like the value in the "Club Name" column to be linked to Table 1, row 3 via a formula instead. 

     

    Hope that clarifies. 

     

    Thanks again. 

    • edhans's avatar
      edhans
      Community Champion
      6 years ago

      I cannot work on it without the source files. When I go into Power Query it says Clubs.xlsx and Sample Table.xlsx are missing.

       

      Just looking though at the data in the DAX model (and I would not do any merge in DAX), you have no filed to merge on, like a customer number, index, or anything else. You cannot say "column 1, row 3" as Power Query has tables, and tables have no concept of row numbers or addressable cells like Excel spreadsheets do.

       

      You should read this article to understand how a Merge works. You can make it somewhat analogous to how a  VLOOKUP works. You want to look up this value in that table, then return in formation from that table. VLOOKUP doesn't understand row numbers either, it just needs a field to scan through and find. That is how a merge works.

      • MC4's avatar
        MC4
        Frequent Visitor
        6 years ago

        Thanks edhans  again for helping out. 

         

        I had a look at the article on Merging, and I have merged queries before, however I wasn't sure how to do it in this instance to achieve the desired result.

         

        Here is a link to the file with no external links to source files so hopefully the Queries remain in tact for you: 

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

         

        Is it possible for you to please show the steps in this file on how the Merge would work to achieve the desired result?

         

        Thanks.