How to pass cell value as the Query value for a query utilizing Power Query

[fowler7878]

Good Morning, 
I am very green to Power Query, so I'm unsure if this is even possible.

Base Goal:
Pass the value from "SELECT_COL" as the UserQuery "let" statement. 

Clarifiying Details:
I have 2 datasets

1) SURVEY_PARAMS

2) SOURCE_LKP_FILE

I have successfully been able to filter down the SOURCE_LKP_FILE until I get just the 1 cell value "SELECT_COL" 

Code to get to just the query value I want to pass into the UserQuery(I can modify my SOURCE_LKP_FILE  and add in or remove anything that's needed to help make this dynamic) below:

let

  Source = SOURCE_LKP_FILE,

  #"Filtered rows" = Table.SelectRows(Source, each [TABLE_NAME] = "FOWLER_LKP_TBL"),

  #"Choose columns" = Table.SelectColumns(#"Filtered rows", {"SELECT_COL"}),

  #"Drill down" = #"Choose columns"{0}[SELECT_COL]

in

  #"Drill down"

I want to take the cell value from above and use it as part/whole of my UserQuery(my user query will just be a series of extract by range) 

I'm a heavy pipeline adf worker and I thought I'd be able to do some form of referencing within here like:

let

  Source = SURVEY_PARAMS,

  #"Inserted text range" = $SELECT_COL

in

  #"Inserted text range"

Any help would be GREATLY appreciated

[BA_Pete]

Hi @fowler7878 ,

Not sure I fully understand your requirements to be honest, but you can reference single 'cells' in other queries by referencing the query and essentially combining the other steps you already did.

For example, you wouldn't necessarily need to do these stpes in your SOURCE_LKP_FILE query:

  #"Filtered rows" = Table.SelectRows(Source, each [TABLE_NAME] = "FOWLER_LKP_TBL"),
  #"Choose columns" = Table.SelectColumns(#"Filtered rows", {"SELECT_COL"}),
  #"Drill down" = #"Choose columns"{0}[SELECT_COL]

Instead, you could do them directly in your SURVEY_PARAMS query, something like this:

let
  Source = SURVEY_PARAMS,
  LKP_Value = Table.SelectRows(SOURCE_LKP_FILE, each [TABLE_NAME] = "FOWLER_LKP_TBL")[SELECT_COL]{0}
in
  LKP_Value

Once you've done this, you should be able to reference the LKP_Value step later in your query and essentially treat it like a variable that contains the 'cell' value, upon which you can prefrom other functions/operations etc.

Pete

[fowler7878]

Hey @BA_Pete, 
This got me almost to the finish line I believe. 
CORE QUESTION: How/can I pass in the LKP_Value as part of my next query's let statement?
Essentially, I want to dynamically reference a lookup table to create the advanced editor script. 

The LKP_Value is just a series of Table.AddColumn functions which I know work because I made them in power query then just copied the values into a lkp table.

The LKP_VALUE is a series of adding columns by their range(example below):
" Table.AddColumn(Source, "SURVEY_COLUMN_1", each Text.Middle([Column1], 0,2), type text) ,
#"Inserted text range 1" = Table.AddColumn(#"Inserted text range", "SURVEY_COLUMN_2", each Text.Middle([Column1], 3,2), type text) .....in #"Removed columns""

I can add the entire let statement as the cell value for the LKP_VALUE, I can put any value necessary into my LKP_TABLE to get this to be dynamic. 

I thought something like this would work:

let Source = #"SOURCE_LKP_FILE",

Source2 = #"SURVEY_PARAMS",

  #"Inserted text range" = #"LKP_Value" 

  in #"Removed columns"

What I want it to look like if the LKP_Value was resolved:

let Source = #"SOURCE_LKP_FILE",

Source2 = #"SURVEY_PARAMS",

#"Inserted text range" = Table.AddColumn(Source, "SURVEY_COLUMN_1", each Text.Middle([Column1], 0,2), type text) ,
#"Inserted text range 1" = Table.AddColumn(#"Inserted text range", "SURVEY_COLUMN_2", each Text.Middle([Column1], 3,4), type text) ,

#"Removed columns" = Table.RemoveColumns(#"Inserted text range 1", {"Column1"}) in #"Removed columns"

[BA_Pete]

Hi @fowler7878 ,

Apologies for the delay in getting back to you on this.

Unfortunately, I think I'm still struggling to understand your requirements here. It sounds like you may be trying to do a conditional merge of some sort but can't be sure.

Are you able to provide a visual example of what you mean please? I.e. An example of the data in each source table, and an example of what the expected outcome would be. The more representative of the real scenario you make these examples, the easier it will be to understand exactly what you're trying to do and get you to a solution.

Pete