Forum Discussion
How to pass cell value as the Query value for a query utilizing Power Query
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
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:
What I want it to look like if the LKP_Value was resolved:
#"Inserted text range 1" = Table.AddColumn(#"Inserted text range", "SURVEY_COLUMN_2", each Text.Middle([Column1], 3,4), type text) ,
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