getting the data from the specific row in the other query when creating a custom column

[u4us1923]

Hello 

I have two tables as below. I'd like to create new custom column (z) in Table2 and add a specific data from Table1.

I couldn't find anything on how I can do this... can you please help me?

Thank you

[Akash_Varuna · Accepted solution]

Hi @u4us1923 Please Follow these 

• Add a Custom Column in Table2

  • In Table2, add a custom column that references a specific value from Table1.
  • Go to the Add Column tab and select Custom Column.

• Use Power Query Formula

  • Use the following formula to extract the value from Table1:
    Table.SelectRows(Table1, each [City] = "NewYork"){0}[Count]
    • Replace "NewYork" with the specific logic for your lookup.
    • {0} selects the first matching row.

• Expand the Logic for Dynamic Lookup

  • To make it dynamic, you can match the rows based on conditions.
    Table.SelectRows(Table1, each [City] = [Y] and [Country] = "USA"){0}[Count]
    If this post helped please do give a kudos and accet this as a solution
    Thanks In Advance

[v-csrikanth · Accepted solution]

Hi @u4us1923 
It's been a while since I heard back from you and I wanted to follow up. Have you had a chance to try the solutions that have been offered?
If the issue has been resolved, can you mark the post as resolved? If you're still experiencing challenges, please feel free to let us know and we'll be happy to continue to help!
Looking forward to your reply!

If the above information is helpful, please give us Kudos and mark the response as Accepted as solution.

Best Regards,
Community Support Team _ C Srikanth

[v-csrikanth]

Hi @u4us1923 

We haven't heard from you since last response and just wanted to check whether the solution provided has worked for you. If yes, please Accept as Solution to help others benefit in the community.
Thank you.

If the above information is helpful, please give us Kudos and mark the response as Accepted as solution.

Best Regards,
Community Support Team _ C Srikanth

[v-csrikanth]

Hi @u4us1923 
In addition to @dufoq3 @Akash_Varuna , Could you please try the below steps.

Steps to add column 'z' in table2 using merge queries in Power BI:

1. Open power query editor → click transform data.
2. Merge tables → select table2, click merge queries, choose table1, match y with city, use left outer join, and click ok.
3. Expand data → click the drop down icon, select count, rename it to z.
4. Fix missing values → replace null with 0 if needed.
5. Apply changes → click close & apply.

Now, table2 has z filled based on table1.

If the above information is helpful, please give us Kudos and mark the response as Accepted as solution.

Best Regards,

Community Support Team _ C Srikanth

[dufoq3]

Hi @u4us1923, add a custom colum with this code:

TableName{row_number}[ColumnName]

example:
TableName = Table1

1st row = 0

ColumnName = Count

Your code for custom column:

Table1{0}[Count]

[u4us1923]

This is not possible because the place of row can change in the future

[Akash_Varuna · Accepted solution]

Hi @u4us1923 Please Follow these 

• Add a Custom Column in Table2

  • In Table2, add a custom column that references a specific value from Table1.
  • Go to the Add Column tab and select Custom Column.

• Use Power Query Formula

  • Use the following formula to extract the value from Table1:
    Table.SelectRows(Table1, each [City] = "NewYork"){0}[Count]
    • Replace "NewYork" with the specific logic for your lookup.
    • {0} selects the first matching row.

• Expand the Logic for Dynamic Lookup

  • To make it dynamic, you can match the rows based on conditions.
    Table.SelectRows(Table1, each [City] = [Y] and [Country] = "USA"){0}[Count]
    If this post helped please do give a kudos and accet this as a solution
    Thanks In Advance

[u4us1923]

thank you .. its work this solution..