Forum Discussion
getting the data from the specific row in the other query when creating a custom column
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
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.
- Use the following formula to extract the value from Table1:
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
- To make it dynamic, you can match the rows based on conditions.
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
7 Replies
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.
- Use the following formula to extract the value from Table1:
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
- To make it dynamic, you can match the rows based on conditions.
thank you .. its work this solution..
This is not possible because the place of row can change in the future
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:- Open power query editor → click transform data.
- Merge tables → select table2, click merge queries, choose table1, match y with city, use left outer join, and click ok.
- Expand data → click the drop down icon, select count, rename it to z.
- Fix missing values → replace null with 0 if needed.
- 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
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 SrikanthHi 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
