Add conditional rows

[Anonymous]

Hi - Any help on the following issue would be greatly appreciated!

End Goal - Import the correct Table2[No.] into Table1, based on [Entry No.]

   I believe the best way to do this is to > Merge Table1[Entry No.] with Table2[Entry No.] >> In Table1 > Expand Table2[No.]

Issue - Table2 does not currently have a column for [Entry No.]. Instead, it has 2 columns for [From Entry No.] and [To Entry No.]. So I need to find the values in between "From" and "To"

Table1:

Document No.	Entry No.	Expected Result
PAY7422	3811638	207075
PAY7422	3811639	207075
PAY7423	3811640	207075
PAY7423	3811641	207075
PAY7425	3811877	207087
PAY7425	3811878	207087
PAY7426	3811879	207087
PAY7426	3811880	207087
PAY7427	3811881	207087
PAY7427	3811882	207087

Table2:

No.	From Entry No.	To Entry No.
207075	3811628	3811641
207076	3811642	3811651
207077	3811652	3811657
207078	3811658	3811809
207079	3811810	3811819
207080	3811820	3811824
207081	3811825	3811838
207082	3811839	3811842
207083	3811843	3811866
207084	3811867	3811870
207085	3811871	3811874
207086	3811875	3811876
207087	3811877	3811882
207088	3811883	3811890

[Anonymous]

This worked great, thank you!!

[Vijay_A_Verma · Accepted solution]

Use this formula in Table1 for Result

= Table.SelectRows(Table2, (x)=> x[#"From Entry No."]<=[#"Entry No."] and x[#"To Entry No."]>=[#"Entry No."]){0}[#"No."]

See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fcm7CcAwDAXAXVS7kOTPk8tskDYY779GUjgmEKHu4Mag87hQVClRNpGW7ZEyGJVm+nf3Or9dOG7xuq42YLXBbfO67e5hG3uN3RK3fnre", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Document No." = _t, #"Entry No." = _t, #"Expected Result" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Document No.", type text}, {"Entry No.", Int64.Type}, {"Expected Result", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Result", each Table.SelectRows(Table2, (x)=> x[#"From Entry No."]<=[#"Entry No."] and x[#"To Entry No."]>=[#"Entry No."]){0}[#"No."])
in
    #"Added Custom"