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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

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
Super User
Super User

@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
Super User
Super User

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
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors