LookupValue Filter search column based on row value

Question

I have 2 tables, one is a table of projects that tell has a row telling me which way to spread the cashflow of the project (linear, backloaded, front loaded, etc.)Table 1Project ID Percent Complete&nbsp; Cashflow Type&nbsp; &nbsp; FORMULA TO RETURN %123&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;.10&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Backloaded&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;.01456&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;.20&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Linear&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; .20789&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;.20&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Frontloaded&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;.21&nbsp;&nbsp;The second table has the cashflow types as column labels and the rows tell me what %spend a project should be at (example below)Table 2Percent Complete&nbsp; Backloaded %&nbsp; &nbsp;Front Loaded%&nbsp; &nbsp;Linear%.10&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; .01&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;.08&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;.10.20&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; .03&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;.21&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; .20&nbsp;&nbsp;I want to do a formula which I BELIEVE is a combination of LookupValue and Filter? Based on the cashflow type in the cell value from table 1 I would like to link to the correct column in table 2 and return the % complete from the correct column.&nbsp;I will also add that the cell value in table 1 is not an exact match to the column name from table 2. I can update them to be exact if needed but I think the switch formula helps there?

alb · Answer

Hi Anonymous&nbsp;
See it all at work in the attached file.
This is best done in Power Query. Place the following M code in a blank query to see the steps.
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyVtJRMtAzBJJOicnZOfmJKakpSrE60UompmZgKSMg6ZOZl5pYBBY2t7CEC7sV5eeVwLTEAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ProjectID = _t, PercentComplete = _t, CashflowType = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ProjectID", Int64.Type}, {"PercentComplete", type number}, {"CashflowType", type text}}),

#"Merged Queries" = Table.NestedJoin(#"Changed Type", {"PercentComplete", "CashflowType"}, Table2, {"PercentComplete", "Type"}, "Table2", JoinKind.LeftOuter),
    #"Expanded Table2" = Table.ExpandTableColumn(#"Merged Queries", "Table2", {"Value"}, {"Value"})
in
    #"Expanded Table2"
&nbsp;and your table 2 needs to be unpivoted for easier transformation
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtAzVNIBkgZQygJMGSrF6oDkjCCCxmDKCKLESCk2FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [PercentComplete = _t, #"Backloaded%" = _t, #"Frontloaded%" = _t, #"Linear%" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"PercentComplete", type number}, {"Backloaded%", type number}, {"Frontloaded%", type number}, {"Linear%", type number}}),

#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"PercentComplete"}, "Type", "Value"),
    #"Sorted Rows" = Table.Sort(#"Unpivoted Columns",{{"Type", Order.Ascending}}),
    #"Replaced Value" = Table.ReplaceValue(#"Sorted Rows","%","",Replacer.ReplaceText,{"Type"})
in
    #"Replaced Value"
&nbsp;

Please accept the solution when done and consider giving a thumbs up if posts are helpful.&nbsp; 
Contact me privately for support with any larger-scale BI needs, tutoring, etc.

&nbsp;

anonymous · Answer

I ended up doing it by If's then lookupvalues pointing at the correct column. I have done something similiar to what you have above in PQ but it became really burdensome on the the functionality of the tool anytime I hit refresh. Thank you for your help

Forum Discussion

LookupValue Filter search column based on row value

2 Replies

Related content

LookupValue with Filter

How to search specific word in table filter Calculations

LookUpValue with Filter

Lookupvalue using Search\ContainsString?

Slicer Search Bar