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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
ThiemenSiemensm
Frequent Visitor

Vertical lookup

Good morning everybody,

 

I have a vertical loopup question using power query. I have two columns in power query, one containing a number, and another containing rows of nulls and lists. The lists in the non-null rows is filled with the numbers from the first columns over which its must total. I want to add an extra column which identifies to which row it totals. See the picture below for the columns.

 

The row value of the new column must be 'Omschrijving' of the row to which it totals. 

e.g. if the Totalling list in index (row) 74 contains 740, then the value of the new column in index (row) 73 must be 'TOTAL PRODUCTION COST'. I already tried to list-expand the Totalling column, but could not further work it out.

Looking forward to your response, thanks!

 

 

ThiemenSiemensm_1-1674466730722.png

 

 

3 REPLIES 3
AlB
Community Champion
Community Champion

@ThiemenSiemensm 

You can simply do a merge of the table with itself, choosing Rijnr. and Totalling as columns for the join, then expand the result to get Omschrijving

 

SU18_powerbi_badge

Please accept the solution when done and consider giving a thumbs up if posts are helpful. 

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

 

AlB
Community Champion
Community Champion

Hi @ThiemenSiemensm 

I don't quite understand what you want to do. Can you elaborate, provide another example? Best if you can share some sample data (in text-tabular format rather than a screen cap or ideally the pbix) and show the expected result

 

SU18_powerbi_badge

Please accept the solution when done and consider giving a thumbs up if posts are helpful. 

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

   

Hi,

 

I hope the two screenshots below give enough information along with my explanation. 

 

The first screenshot is the column containing lists of values, which has been expanded in the second screenshot, so that list contains [520;620;690;730]. I want to look for that values in the column 'RijNr.' and the new column must contain the value 'Omschrijving'. So all the values for which 'RijNr.' is in [520;620;690;730] has new column value TOTAL PRODUCTION COST. Just like a lookup in Excel, see example shown below. Hope this clarifies things!

 

ThiemenSiemensm_3-1674468776153.png

 

ThiemenSiemensm_2-1674468745726.png

ThiemenSiemensm_4-1674469556718.png

 

 

 

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

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

Top Kudoed Authors