LookupValue Filter search column based on row value

[Anonymous]

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 1

Project ID Percent Complete  Cashflow Type    FORMULA TO RETURN %

123               .10                         Backloaded             .01

456               .20                         Linear                      .20

789               .20                         Frontloaded             .21 

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 2

Percent Complete  Backloaded %   Front Loaded%   Linear%

.10                              .01                     .08                     .10

.20                              .03                     .21                      .20 

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.

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]

Hi @Anonymous 

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"

 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"

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

[Anonymous]

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