Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
MC4
Frequent Visitor

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. 

1 ACCEPTED SOLUTION
watkinnc
Super User
Super User

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

I’m usually answering from my phone, which means the results are visualized only in my mind. You’ll need to use my answer to know that it works—but it will work!!

View solution in original post

14 REPLIES 14
watkinnc
Super User
Super User

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

I’m usually answering from my phone, which means the results are visualized only in my mind. You’ll need to use my answer to know that it works—but it will work!!
MC4
Frequent Visitor

Thank you @watkinnc ! 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! 

edhans
Super User
Super User

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.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
MC4
Frequent Visitor

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.

See links below for posting some data. I cannot figure out exactly what is going on with the descriptions you are giving.

 

How to get good help fast. Help us help you.
How to Get Your Question Answered Quickly
How to provide sample data in the Power BI Forum



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
MC4
Frequent Visitor

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. 

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.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
MC4
Frequent Visitor

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. 

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.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
MC4
Frequent Visitor

Thanks again @edhans 

 

The reason I want to be able to get the club name from the other table is because I want to apply this step in multiple queries with different club names and be able to update the source data from say "Club 3" to "Manchester United" and have this flow through all the queries. 

 

So essentially I am trying to find someway to link the two tables so the computer knows to look at Club 3 from the other table.

 

Thanks. 

But why do you expect it to know which is Club 3 vs Club 2? How are you linking the data?



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

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
Frequent Visitor

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!

 

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.

 

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors