# PowerPivot Indirect  Dynamic Column Reference



## jolee217 (Aug 24, 2018)

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: 


PartDefault VendorSupplier1Supplier2Supplier3Price @ Default SupplierMaterial1Supplier11.001.251.30Material2Supplier24.104.004.15Material3Supplier33.703.403.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 (Aug 27, 2018)

Anyone have any thoughts?


----------



## horseyride (Aug 28, 2018)

```
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 (Aug 28, 2018)

horseyride said:


> ```
> let
> Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
> Index1 = Table.AddIndexColumn(Source, "Index", 0, 1),
> ...




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

Thoughts?


----------



## horseyride (Aug 28, 2018)

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


----------

