Power Query Lookup range changed based on Cell value

[Negi1984]

Hi All,

I have a Master sheet having wk number mentioned in first column. I want to apply vlookup on name based on value mentioned in Wk number column.

For example :- if wk number is less than 26 than vlookup apply from Group 1 sheet and pick the "Current Hrs" value based on Resource Name.

can anybody suggest me , how dynamically we can change the lookup table and pick the value.

Below is the sample data.

https://1drv.ms/x/s!Ap80Ku6M2Tw5gTyVsOwG-9-qNL-2

Regards,

Rajender

 

[Ron Coderre]

Your title indicates that this is a Power Query question. Yet, it seems to have nothing to do with Power Query. Am I missing something?

Anyway, not exactly sure what you want to do....but....
Using your posted example....
This VLOOKUP formula, copied down, looks up the Resource Name on the Master Sheet and finds the Current Hrs from either Table13 or Table134 (depending on whether the Week No is less than 26 or not:

E2: =VLOOKUP(Table1[@[Resource Name]],CHOOSE((Table1[@[Week no]]<26)+1,Table13[[#All],[Resource Name]:[Current Hrs]],Table134[[#All],[Resource Name]:[Current Hrs]]),2,0)

Is that something you can work with?

 

[Negi1984]

Hi Ron,

Sorry if I was not clear, instead of formula based solution I am looking , how to do the same with Power Query.
it will be very thankful to you if you can assist for the same as well.

Regards
Rajender

 

[Negi1984]

Hi Ron,

I recheck , my query and found not correctly mentioned , I am looking for solution if my week no is less than 26 than pick the values from Group 1 sheet and if its greater than 26 than from Group 2 sheet.

 

[Ron Coderre]

Using your posted workbook...Here are the Power Queries I used:
Table13

let
    Source = Excel.CurrentWorkbook(){[Name="Table13"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", Int64.Type}, {"Resource Name", type text}, {"Current Hrs", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Group", each "Group 1", type text)
in
    #"Added Custom"

Table134

let
    Source = Excel.CurrentWorkbook(){[Name="Table134"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", Int64.Type}, {"Resource Name", type text}, {"Current Hrs", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Group", each "Group 2", type text)
in
    #"Added Custom"

Append1

let
    Source = Table.Combine({Table13, Table134})
in
    Source

Table1

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Week no", Int64.Type}, {"Resource Name", type text}, {"Current Hrs", Int64.Type}}),
    #"Added Conditional Column" = Table.AddColumn(#"Changed Type", "Group", each if [Week no] <= 26 then "Group 1" else "Group 2", type text )
in
    #"Added Conditional Column"

Merge1

 let
    Source = Table.NestedJoin(Table1,{"Resource Name", "Group"},Append1,{"Resource Name", "Group"},"Append1",JoinKind.LeftOuter),
    #"Expanded Append1" = Table.ExpandTableColumn(Source, "Append1", {"Current Hrs"}, {"Current Hrs.1"})
in
    #"Expanded Append1"

Is that something you can work with?

 

[Negi1984]

Hi Ron

Thanks a ton, This is what I want. you provided the solution in very explained and simpler way.
I was thinking it will be done via some complex M query.
Have a nice day to you.

Using your posted workbook...Here are the Power Queries I used:
Table13

let
    Source = Excel.CurrentWorkbook(){[Name="Table13"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", Int64.Type}, {"Resource Name", type text}, {"Current Hrs", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Group", each "Group 1", type text)
in
    #"Added Custom"

Table134

let
    Source = Excel.CurrentWorkbook(){[Name="Table134"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", Int64.Type}, {"Resource Name", type text}, {"Current Hrs", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Group", each "Group 2", type text)
in
    #"Added Custom"

Append1

let
    Source = Table.Combine({Table13, Table134})
in
    Source

Table1

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Week no", Int64.Type}, {"Resource Name", type text}, {"Current Hrs", Int64.Type}}),
    #"Added Conditional Column" = Table.AddColumn(#"Changed Type", "Group", each if [Week no] <= 26 then "Group 1" else "Group 2", type text )
in
    #"Added Conditional Column"

Merge1

 let
    Source = Table.NestedJoin(Table1,{"Resource Name", "Group"},Append1,{"Resource Name", "Group"},"Append1",JoinKind.LeftOuter),
    #"Expanded Append1" = Table.ExpandTableColumn(Source, "Append1", {"Current Hrs"}, {"Current Hrs.1"})
in
    #"Expanded Append1"

Is that something you can work with?