0

I have a spreadsheet containing 20 individual queries that can be individually called using a single append query. The results of the append query drives a system I developed to batch-edit documents on an online service. I need to pack my project into a design that would allow easy modification of the append query without going into an advanced editor and manually changing the query references.

The main reason why I don't just append 20 queries together is due to the nature of the queries throwing an error when the source is empty, which causes the append query to fail after 30 minutes of running and all the data acquired during the update being dumped into oblivion.

Currently, I have an input panel designed for an end-user to enter a number that matches the online service's number, and a formula in a deep sheet that calculates the exact queries needed to successfully update without error. My issue is that my append query looks like it could take a variable in direct replacement of the query references, but the Append query does not recognize the variable as a list of query references.

The initial append query looked like this:

let
    Source = Table.Combine({API_QUERY_1, API_QUERY_2})
in
    Source

note: for this post's sake I didn't create an append query of 20 due to the clutter it'd make on here. Two is sufficient to make a working example

I attempted to follow Peter Albert's solution so that my M code would look like this:

let
QueryRef = Excel.Workbook(File.Contents(GetValue("QuerySize"))),
    Source = Table.Combine({QueryRef})
in
    Source

note: I have a supplemental function named GetValue with this query just like Peter has

QuerySize is a named-cell in my spreadsheet that prints out an identical copy of the references that were hard-coded between the curly braces. The resulting error is the following:

DataFormat.Error: The supplied file path must be a valid absolute path.
Details:
    API_QUERY_1, API_QUERY_2

What do I need to do to change my append query such that it behaves dynamically, based off a cell value?

EDIT: Contents of the GetValue Function

(rangeName) => 
    Excel.CurrentWorkbook(){[Name=rangeName]}[Content]{0}[Column1]

Contents of the named cell QuerySize

API_QUERY_1, API_QUERY_2
Mayukh Bhattacharya
  • 12,541
  • 5
  • 21
  • 32
Jaz J.
  • 3
  • 4

1 Answers1

1

Try

let QueryRef = GetValue("QuerySize"),
separate=Text.Split(QueryRef,", "),
combined=Table.Combine(List.Transform(separate, each Record.Field(Record.SelectFields(#shared,{_}),_)))
in combined
horseyride
  • 17,007
  • 2
  • 11
  • 22