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:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Part[/TD]
[TD]Default Vendor[/TD]
[TD]Supplier1[/TD]
[TD]Supplier2[/TD]
[TD]Supplier3[/TD]
[TD]Price @ Default Supplier[/TD]
[/TR]
[TR]
[TD]Material1[/TD]
[TD]Supplier1[/TD]
[TD]1.00[/TD]
[TD]1.25[/TD]
[TD]1.30[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Material2[/TD]
[TD]Supplier2[/TD]
[TD]4.10[/TD]
[TD]4.00[/TD]
[TD]4.15[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Material3[/TD]
[TD]Supplier3[/TD]
[TD]3.70[/TD]
[TD]3.40[/TD]
[TD]3.00[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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...