Power Query Value from Named Range based on Another Column

[jeff4smith]

I have a Query that includes a column with list of last names, i.e. Smith, Jones, etc. In my worksheet there is a named range of Week_Smith, Week_Jones, etc and this named range is a single cell, which contains a date. I would like to add a new column to the Query to return the named range of each person. I was able to create a conditional column that has the correct named range using the code below, but I am not sure how to instead return the value of that named range.

#"Inserted Merged Column" = Table.AddColumn(#"Changed Type", "Merged", each Text.Combine({"Week_", [Name]}), type text),

Named_Range	Name	Highlights
Week_Smith	Smith	First highlight
Week_Smith	Smith	Second highlight
Week_Smith	Smith	Third highlight
Week_Jones	Jones	First highlight
Week_Jones	Jones	Second highlight
Week_Jones	Jones	Third highlight

 

[Norie]

When in Power Query go to New Query>New Source>Other Sources>Blank Query.

Open the new query in the Advanced Editor and add this code.

let GetNamedRange=(NamedRange) =>
 
let
    name = Excel.CurrentWorkbook(){[Name=NamedRange]}[Content],
    value = name{0}[Column1]
in
    value

in GetNamedRange

Go back to the original query, go to Add Column>Invoke Custom Function.

In the Invoke Custom Function select the function you created above as the Function Query and select the appropriate column from the Named Range dropdown.

 

[jeff4smith]

Excellent. Thanks for the quick reply and simple solution! I appreciate it!

 

[Norie]

No problem.

By the way, you could alter the code I posted so it works when you pass it the name rather than the value from the column you created.

That would save you having to create a new column.

let GetNamedRange=(Person) =>
 
let
    name = Excel.CurrentWorkbook(){[Name=Text.Combine({"Week_", Person}) ]}[Content],
    value = name{0}[Column1]
in
    value

in GetNamedRange