Import a value from another table if 3 conditions are met

[dliddle]

Hi

I am trying to get a custom column in power query to lookup a value from another table if 3 criteria are met.  In Excel I'd use if(and( but the conditional column option only lets me use columns from the existing table not look up from a second table.

Table 1

Position (yds)    Road Name    

0                        a1

10                      a1

Table 2

Road Name       Start Yds    End Yds        Block

a1                      5                 20                Fred

Essentially i want to show against each marker that falls within the block "Fred"

Criteria 1     Table1 Road Name = Table2 Road Name

Criteria 2     Table1 Position(yds) >= Table2 Start Yds

Criteria 3     Table1 Position(yds) >= Table2 End Yds

this would then show null in Table 1 against Position 0 and "Fred" against Position 10 as follows.

Table 1

Position (yds)    Road Name    Custom

0                        a1                   null

10                      a1                   Fred

Alternately is it posible to unpivot the Start and End Mileage and have the integer values between diplayed which I could then concatenate Road&Position in each table and merge tables

For example

Table 2 (Unpivoted)

Road     Yards   Block

a1           5        Fred

a1           6        Fred

a1           7        Fred

....

a1           20       Fred

[V-lianl-msft · Accepted solution]

Hi @dliddle ,

Please try the follow steps in power query:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlDSUXI0VIrViVYyhLFjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Position(YDS)" = _t, #"Road Name" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Position(YDS)", Int64.Type}, {"Road Name", type text}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Road Name"}, #"Table (2)", {"Road Name"}, "Table (2)", JoinKind.LeftOuter),
    #"Expanded Table (2)" = Table.ExpandTableColumn(#"Merged Queries", "Table (2)", {"Start Yds", "End Yds", "Block"}, {"Table (2).Start Yds", "Table (2).End Yds", "Table (2).Block"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Table (2)", "Custom", each if [#"Position(YDS)"] >=[#"Table (2).Start Yds"] and [#"Position(YDS)"]<=[#"Table (2).End Yds"] then [#"Table (2).Block"] else null),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Table (2).Start Yds", "Table (2).End Yds", "Table (2).Block"})
in
    #"Removed Columns"

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[dliddle]

Hi Liang, 

I followed this process and got an error for the rows that should have shown a value.  I did however manage to work around (changed errors to 1) this and in the process figured out that I needed to make a duplicate table too as I may have occassions where I have multiple blocks in the same location.  I can now have a filtered table showing just the items under block and create a relationship to use this as a filter.

Thanks for your help you got me on the right track.