Vertical lookup

[ThiemenSiemensm]

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!

[AlB]

@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

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]

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

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.

[ThiemenSiemensm]

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!