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

user_18037247 0
Viewed 931

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:

Lookup master Table

My data is as follows:

Data

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:

Resulting Table after lookup & adding column

Can someone point me on how to do this?

Any help is greatly appreciated. Thanks in Advance

powerquery excel
1 Answers

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"
user_92642300
Related