Append queries dynamically in Power Query

Hi ImkeF thanks for your advice. I am stuck in two parts tho;

1. What should I do once I have created a parameter? I left all default settings, and wrote "Sales_" as my current value in the parameters.
2. What function(s) should I use to create a table with one row for each of the table, since #sections can't do the trick for me?

Looking forward to hearing from you. Thanks.

Hi ImkeF after trying for a few weeks, here is what I did.

This is the beginning of the custom function:

(List) =>
let
Source = Table.Combine({
List
}),

Which I try to invoke from a list of queries I want, and it returned an error:

An error occurred in the ‘’ query. Expression.Error: We cannot convert the value "Sales_Y2017-Y2018" to type Table.
Details:
    Value=Sales_Y2017-Y2018
    Type=[Type]

So when I tried to work with this, it works fine, but still this function is not dynamic as it still requires me to select the files, and edit the function when the number of files I want combined is not the same with the number of variables I declared below.

(FileName1 as table, FileName2 as table, FileName3 as table) =>
let
    Source = Table.Combine({
			FileName1, FileName2, FileName3
		}),

Do you have any advice how I can move forward? Thanks.

Hi Alex_Ooi ,

the idea is to get rid of the whole folder "Sales batch" and do everyting in one table. If there are perfomance issues, you can use Function.InvokeAfter to determine a duration that has to be waited before the next call happens:

let
    // To make it easy to paste here, I've included the function that's been called in the query itself
    // but this would probably be a function that you're going to define a function separately that replicates the queries for your Sales Batch queries (using 2 parameters: Start and End date) 
    myFunction = (myStart as date, myEnd as date) => {Number.From(myStart)..Number.From(myEnd)},
    
// Sample data, replace by a table of your own
Source = Table.FromRows(Json.Doc", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Start = _t, End = _t]),

    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Start", type date}, {"End", type date}}),

// Function.InvokeAfter is optional, adjust duration if necessary
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Function.InvokeAfter(() => myFunction([Start], [End]), #duration(0,0,0,5)))
in
    #"Added Custom"

Hi ImkeF thanks for your reply.

I have a slight problem in the syntax error in the solution you provided to see if I could make sense with my table(s). The offending line is below, with the syntax error message, "Token Comma Expected":

Source = Table.FromRows(Json.Doc", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Start = _t, End = _t]),

But if I understand you perfectly, most likely I am not able to "do everything in one table" since I am connecting to my database using multiple SQL scripts by adjusting the date ranges each of these scripts should look out for. The performance issues I highlighted in my post initially was not related to Power BI, but rather that of my server. So I am quite sure that the manipulations in Power BI wouldn't help since the performance issue lies in the data source itself.

I am aware that there could be other workarounds, like importing the table/view I want from the database, and enable query folding to allow manipulation to take place in Power Query, but that would prove too much work on my side reinventing the wheel. So I have never worked on other solutions before.

Anyway, as a part of learning process, I am still keen to see your solution. Would you mind to help me out with the syntax error above? Much appreciated as always.

Cheers.

Hi Alex_Ooi ,

very sorry about the broken code. (Have inserted the comments afterwards here in the forum, so must have messed sth up there)

let
    // To make it easy to paste here, I've included the function that's been called in the query itself
    // but this would probably be a function that you're going to define a function separately that replicates the queries for your Sales Batch queries (using 2 parameters: Start and End date) 
    myFunction = (myStart as date, myEnd as date) => {Number.From(myStart)..Number.From(myEnd)},
    
// Sample data, replace by a table of your own
    Source= Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDUByIjA0NzJR0lY0N9QyMIJ1YHSc4CWc5CKTYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Start = _t, End = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Start", type date}, {"End", type date}}),

// Function.InvokeAfter is optional, adjust duration if necessary
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Function.InvokeAfter(() => myFunction([Start], [End]), #duration(0,0,0,5)))
in
    #"Added Custom"

I am happy to adjust your code for the database if you paste it here. I will integrate the 2 parameters you'll need for the start- and end -date. 

Thank you ImkeF. The code you provided does look quite promising.

The code I am using for the values I am getting from the database are as follow. Here is a snippet of Sales_Y2017-Y2018 query:

let
    Source = Value.NativeQuery(PostgreSQL.Database("MyHost", "DatabaseName"), "SELECT
    ""Site Name"", ""Booking ID"", ""Booking Date""
    // truncated for simplicity of presentation
    FROM dbo.""MyView""
    WHERE ""Booking Date"" >= '2017-08-30' AND ""Booking Date"" < '2019-01-01',
    null, [EnableFolding = false])
in
    Source

 Nothing much changes for the rest of the structure in the rest of the queries stored in the "Sales Batch" folder except the the parameters defined in the WHERE clause. As explained earlier I am not able to set my SQL script parameter to the following as my database is unfortunately not having the bandwidth/capacity to run the query that way. 

WHERE ""Booking Date"" >= '2017-08-30'

So I was advised by my developers to break my queries down to a few batches, hence "Sales Batch" was created.

Looking forward to hearing from you soon. Thanks again!

Hi Alex_Ooi ,

please try this:

let
// To make it easy to paste here, I've included the function that's been called in the query itself
// but this would probably be a function that you're going to define a function separately that replicates the queries for your Sales Batch queries (using 2 parameters: Start and End date) 
myFunction = (myStart as text, myEnd as text) => 

let
Source = Value.NativeQuery(PostgreSQL.Database("MyHost", "DatabaseName"), "SELECT
""Site Name"", ""Booking ID"", ""Booking Date""
// truncated for simplicity of presentation
FROM dbo.""MyView""
WHERE ""Booking Date"" >= ' " & myStart & "' AND ""Booking Date"" < ' " & myEnd & "'",
null, [EnableFolding = false])
in
Source
,

// Sample data, replace by a table of your own
Source= Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDUByIjA0NzJR0lY0N9QyMIJ1YHSc4CWc5CKTYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Start = _t, End = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Start", type date}, {"End", type date}}),
AddStartString = Table.AddColumn(#"Changed Type", "StartString", each Text.From(Date.Year([Start])) & "-" & Text.PadStart(Text.From(Date.Month([Start])),2,"0") & "-" & Text.PadStart(Text.From(Date.Day([Start])),2,"0")),
AddEndString = Table.AddColumn(AddStartString, "EndString", each Text.From(Date.Year([End])) & "-" & Text.PadStart(Text.From(Date.Month([End])),2,"0") & "-" & Text.PadStart(Text.From(Date.Day([End])),2,"0")),

// Function.InvokeAfter is optional, adjust duration if necessary
CallFunction = Table.AddColumn(AddEndString, "Custom", each Function.InvokeAfter(() => myFunction([StartString], [EndString]), #duration(0,0,0,5)))
in
CallFunction

 I'm a bit surprised that you're setting the "enable folding"-parameter to false. (But maybe this will just be ignored..)

Hi ImkeF , I'm terribly sorry that after struggling to clean the code of the syntax error, I still couldn't pass through to see the expected result. The offending line is below, with an error message showing, "token comma expected".

WHERE ""Booking Date"" >= ' " & myStart & "' AND ""Booking Date"" < ' " & myEnd & "'",

I have tried many permutations of where to put the double quotes on almost every possible place and I still have an error.

To share with you as to why I disabled query folding (on purpose), it was because I'd had bad experiences working with this connector where I then figured a workaround (by connecting to ODBC). However, after my company has migrated its hosting from AWS to GCP, I am no longer able to connect the data source via ODBC for weird reasons. So to avoid this sort of problems from happening in future, I had to disable that query-folding.

I understand that theoretically by enabling query folding would be more performant, but for Postgres connector, it isn't the case practically. Nonetheless, I still hope I could connect that database via ODBC somehow again; still staying query-ious about it!

Thanks again, hope to hear from you on the correction so I could learn the syntax!

Cheers,

Alex