Power Query: Lookup Column Headers in Another Table, Return Corresponding Column Value

Hi all,

I'm still fairly new to Power Query and am attempting to do a lookup based on a Column's Header in another table. I know how to use List.PositionOf to find the row for a specific value in a column in another table, but I'm not sure how to limit this to just the column's header itself rather than the individual values in that column. For instance, if I have a Data table with column headers Column1, Column2, and Column3 with n rows of data and another Lookup table with columns Header and Letter where the three row contain values Column1 | A, Column2 | B, and Column3 | C I would like to be able to look up the header names from Data in column Header and return the Letter from Lookup.

I cannot post an example file currently. Please let me know if you require any clarification.

Is this possible?

I am working from the office and am unable to attach files from our network. I've attempted to attach an image to this reply.

PQ Example.PNG

as you can see this is possible

Column1	Column2	Column3	Column4	Column5		Header	Letter
3	9	6	7	5		Column1	A
2	3	10	3	10		Column2	B
8	4	1	1	3		Column3	C
						Column4	D
						Column5	E
Column1	Column2	Column3	Column4	Column5
A	B	C	D	E
A	B	C	D	E
A	B	C	D	E

unpivot first table , merge with second by columns, expand, remove unnecessary columns, group, add custom column with list, extract values, remove column with tables, pivot header by custom, then split each column by delimiter and use Table.FirstN()

there is a simpler way but have no time, sorry

Thank you, Sandy. If anyone has a simpler way, preferably without merging tables, I would greatly appreciate it. Leaving the question open for now.

Hi Sandy, thank you again for your time. I believe I may have not communicated myself effectively. I am not trying to convert the Lookup table into the format of the Data table, I am trying to look up the Column headers from the Data table in the Lookup table and return the Letter in each cell of the Data table. Is this possible?

maybe easier will be with formula(s) but this is not my melody

btw. LOOKUP in PQ is just Merge

I have been able to perform lookups using List.PositionOf. If there was only some way to refer the Column Header of each cell I believe this could be performed quite easily, I'm just not sure how to do this/if it's possible.

so try try and try then you will know

ok, have a nice day

I found a simple way to return the columns' header:

Formula:

Please Login or Register  to view this content.

Where i is the column index. Combining this result with List.PositionOf can return the desired value from the Lookup table.

post a whole M code after finish

It's slightly messy but here is the final applied step:

Formula:

Please Login or Register  to view this content.

The goal was to look up the number of Hours in the Lookup table based on the column header 1/1/2022 and divide the value in the cell by the returned number of hours. If anyone knows of a way to simplify, I'd appreciate it.

first read big yellow banner at the top of this site and do this because your picture has nothing to do with the last post #14

Attaching example file with solution.

date is hardcoded, if it will be changed in the sources you'll see something like that

experr.png

Hi Sandy, unfortunately I still have to work some of the details out. I didn't want to ask another question in the middle of a post that was originally just trying to find a way to return a column header. I am hoping to eventually find a solution that would iterate through all "dated" columns dynamically by their index number to account for varying amounts of columns and varying column names with just one transformation; a way to loop through column indexes 2-12. I don't have any experience writing loops in M and don't know the syntax. If you're up for it, any help would be appreciated. Otherwise I can create a new thread.