Excel Power Query - add conditional column by looking up data from another Table-CodePudding

I am looking to add a conditional column in power query by looking up data from another table.

As an example, my lookup data is as follows:

My data is as follows:

Now I want to check if the number in my data is between Begin & End of my Master data & if the condition is satisfied, then I want to add a column by coping the corresponding "Digits" Cell to my Data. The result should look like this:

Can someone point me on how to do this?

Any help is greatly appreciated. Thanks in Advance

CodePudding user response：

Load your your top table into powerquery with the query name LookupQuery and file ... close and load. It should have the column names you show

Then load the middle source table into powerquery and Add column ... custom column... with formula

= Table.SelectRows(LookupQuery,(x) => [Data] >= x[Begin] and [Data] <=x[End] )[Digits]{0}

full code:

let Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Data", Int64.Type}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Table.SelectRows(LookupQuery,(x) => [Data] >= x[Begin] and [Data] <=x[End] )[Digits]{0})
in #"Added Custom"

another way is to do a match based on length of the digits. This would work better for large data sets Load your middle source data into powerquery and

add column ... custom column ... with formula

=  Text.Length(Text.From([Data]))

merge your data with with lookup query and replace the formula in the formula bar with :

= Table.NestedJoin(#"Added Custom", {"Custom"}, Table.AddColumn(LookupQuery, "Custom", each Text.Length(Text.From([Begin]))), {"Custom"}, "LookupQuery", JoinKind.LeftOuter)

expand the Digits column

full code for the 2nd method:

let Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Data", Int64.Type}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Text.Length(Text.From([Data]))),
#"Merged Queries" = Table.NestedJoin(#"Added Custom", {"Custom"}, Table.AddColumn(LookupQuery, "Custom", each Text.Length(Text.From([Begin]))), {"Custom"}, "LookupQuery", JoinKind.LeftOuter),
#"Expanded LookupQuery" = Table.ExpandTableColumn(#"Merged Queries", "LookupQuery", {"Digits"}, {"Digits"})
in #"Expanded LookupQuery"