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

**takoyaki** · 10-24-2022, 11:23 AM

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?

**AliGW** · 10-24-2022, 11:24 AM

We are you unab;e to post a sample workbook?

**takoyaki** · 10-24-2022, 11:25 AM

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

**sandy666** · 10-24-2022, 12:02 PM

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

**takoyaki** · 10-24-2022, 12:05 PM

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

**sandy666** · 10-24-2022, 12:09 PM

sure, with table2 only

let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    #"Duplicated Column" = Table.DuplicateColumn(Source, "Letter", "Letter - Copy"),
    #"Duplicated Column1" = Table.DuplicateColumn(#"Duplicated Column", "Letter - Copy", "Letter - Copy - Copy"),
    #"Transposed Table" = Table.Transpose(#"Duplicated Column1"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true])
in
    #"Promoted Headers"

Header	Letter	Column1	Column2	Column3	Column4	Column5
Column1	A	A	B	C	D	E
Column2	B	A	B	C	D	E
Column3	C	A	B	C	D	E
Column4	D
Column5	E

next time if you posting a picture turn off filter buttons !!! but usually we need excel file

**takoyaki** · 10-24-2022, 12:16 PM

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?

**sandy666** · 10-24-2022, 12:18 PM

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

btw. LOOKUP in PQ is just Merge

**takoyaki** · 10-24-2022, 12:21 PM

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.

**sandy666** · 10-24-2022, 12:25 PM

so try try and try then you will know

ok, have a nice day

**sandy666** · 10-24-2022, 01:23 PM

I don't need any List.PositionOf because simple Append feature (works like LOOKUP) will do that for me, matches the appropriate columns in one table with the corresponding columns in the other table

// QueryRes
let
    tbl1 = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Text = Table.TransformColumnTypes(Table.UnpivotOtherColumns(tbl1, {}, "Attribute", "Value"),{{"Value", type text}}),
    SBP = Table.ExpandListColumn(Table.TransformColumns(Text, {{"Value", Splitter.SplitTextByPositions({0, 0}, false), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Value"),
    Null = Table.SelectRows(Table.TransformColumnTypes(SBP,{{"Value", Int64.Type}}), each [Value] = null),
    Pivot = Table.Pivot(Null, List.Distinct(Null[Attribute]), "Attribute", "Value", List.Sum),
    tbl2 = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    Result = Table.FillDown(Table.Combine({Table.Combine({Table.Pivot(tbl2, List.Distinct(tbl2[Header]), "Header", "Letter"), Pivot}), Pivot}),{"Column1", "Column2", "Column3", "Column4", "Column5"})
in
    Result

**takoyaki** · 10-24-2022, 05:14 PM

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

Formula:

Table.ColumnNames(#"Previous Step"){i}

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

**sandy666** · 10-24-2022, 05:16 PM

post a whole M code after finish

**takoyaki** · 10-24-2022, 08:26 PM

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

Formula:

= Table.TransformColumns(#"Changed Type", {{"1/1/2022", each _ / Hours[Hours]{List.PositionOf(Hours[Period],Date.FromText(Table.ColumnNames(#"Changed Type"){Table.Schema(#"Changed Type"){[Name="1/1/2022"]}[Position]}))}}})

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.

**sandy666** · 10-24-2022, 08:34 PM

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

**takoyaki** · 10-24-2022, 08:35 PM

Attaching example file with solution.

**sandy666** · 10-24-2022, 08:57 PM

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

experr.png

**takoyaki** · 10-24-2022, 09:01 PM

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.

**sandy666** · 10-24-2022, 09:13 PM

don't create a new thread because the problem remains the same, imho will be better if you stay here. I will be watching with interest how you handle it

maybe try List.Generate

List.Generate(initial as function, condition as function, next as function, optional selector as nullable function) as list

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

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Thread Information

Users Browsing this Thread

Similar Threads

Power Query Combine Payroll files from Folder where column headers do not align

Power Query - Add another column to Table.TransformColumns

[SOLVED] Power Query and imported table headers

[SOLVED] Variable Column Headers in or attached to a Power Pivot Table

[SOLVED] Lookup (index/match) and return column headers

Power Query - add column with Table name

Return closest value less than given number matching another column - Power Query ?

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions