PowerPivot Indirect Dynamic Column Reference

[jolee217]

It seems like such an easy thing: As I would do in excel, I just want to use a cell in my row to determine which column to return value from.

An example of my (VERY simplified) table "tTable" is:

Part	Default Vendor	Supplier1	Supplier2	Supplier3	Price @ Default Supplier
Material1	Supplier1	1.00	1.25	1.30
Material2	Supplier2	4.10	4.00	4.15
Material3	Supplier3	3.70	3.40	3.00

<tbody>
</tbody>

In excel I would write this in F2:

=VLOOKUP([@Part], tTable, MATCH([@Vendor],tTable[#Headers],0), FALSE) = $1.00
OR
=ADDRESS(ROW(),COLUMN(INDIRECT("tTable["&tTable[@Vendor]&"]")))=$1.00


However, I'm trying to do this same thing in PowerPivot. My data is essentially the same. What I WANT to do is:

=LOOKUPVALUE(INDIRECT("tTable["&[Vendor]&"]"), [Part], [Part])

Which obviously doesn't work. I am out of ideas and cannot find a solution online. Excel does not work since the data set is far too large, and powerpivot is my only alternative.

Is there a formula for looking up a dynamic column in PowerPivot?

 

[jolee217]

Anyone have any thoughts?

 

[horseyride]

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Index1 = Table.AddIndexColumn(Source, "Index", 0, 1),
    Unpivot1 = Table.UnpivotOtherColumns(Index1, {"Part", "Default Vendor", "Index"}, "Attribute", "Value"),
    #"Merged Queries" = Table.NestedJoin(Index1,{"Default Vendor", "Index"},Unpivot1,{"Attribute", "Index"},"Table2",JoinKind.LeftOuter),
    #"Expanded Table9" = Table.ExpandTableColumn(#"Merged Queries", "Table2", {"Value"}, {"Price @ Default Supplier"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Table9",{"Index"})
in #"Removed Columns"

Table1=table with header rows, all columns except the Price@DefaultShipper column

 

[jolee217]

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Index1 = Table.AddIndexColumn(Source, "Index", 0, 1),
    Unpivot1 = Table.UnpivotOtherColumns(Index1, {"Part", "Default Vendor", "Index"}, "Attribute", "Value"),
    #"Merged Queries" = Table.NestedJoin(Index1,{"Default Vendor", "Index"},Unpivot1,{"Attribute", "Index"},"Table2",JoinKind.LeftOuter),
    #"Expanded Table9" = Table.ExpandTableColumn(#"Merged Queries", "Table2", {"Value"}, {"Price @ Default Supplier"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Table9",{"Index"})
in #"Removed Columns"

Table1=table with header rows, all columns except the Price@DefaultShipper column

I am unsure how to add/apply this code. I'm inside PowerPivot, not PowerBI.

Thoughts?

 

[horseyride]

This is M language for PowerQuery, for which the step by step for my version of Excel would be:

Highlight excel range of source data [here from word "Part" in top left to number "3.00" in bottom right]
Data..From Table/Range...
[x] my table has headers
Home...Advanced Editor...
[Paste code above in replacement of code on screen]
[Done button]
File...Close and Load...