Forum Discussion
Append queries dynamically in Power Query
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
SourceNothing 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 CallFunctionI'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