Append queries dynamically in Power Query

[Alex_Ooi]

Hi all,

A little background of this request:

- The tables in "Sales Batch" query folder are created from querying my Postgresql database

- Due to performance issues, the best way is to break the queries down in chunks like this instead of setting an open-ended condition in my query like

WHERE date >= '2017-01-01'

With this, I can foresee myself updating the queries in case Sales_Y2020 gets too large and start giving performance issues again. So, I might potentially need to break the queries down further. That said, when I append the tables together in "Sales" from "Other Queries" query folder, I need to manually configure them as per the red box below.

Is there a way for Power Query to dynamically append these tables together in "Sales" from "Other Queries"?

Thanks

[AlB]

Hi @Alex_Ooi 

#sections can probably work for you

Take a look at https://radacad.com/get-list-of-queries-in-power-bi 

Please mark the question solved when done and consider giving kudos if posts are helpful.

 Cheers 

[Alex_Ooi]

Hey @AlB , thanks for the reply. I managed to get to where I wanted to be, only thing is... The whole table went blank after I hit close & apply.

[AlB]

You could extract the list of queries you are interested in using #sections and then build the full expression on a string and use 

Expression.Evaluate

Please mark the question solved when done and consider giving kudos if posts are helpful.

 Cheers 

[Alex_Ooi]

I have never worked with Expression.Evaluate before in Power Query. Can you share more with me? Do I add this step at the end of my query or something? I've read the documentation you linked me, but I still don't quite grasp how does that help in the situation here. Any detailed guide is much appreciate. Thank you so much already, @AlB 

[AlB]

You do not actually need it Expression.Evaluate. It would be complicating things unnecessarily. You can just use #sections to get the list of all queries, filter out the ones you don't want and then combine (append) the remaining ones to get the final result. This example selects and appends all queries whose name starts with "Table1":

let
    Source=#sections,
    Section1 = Source[Section1],
    #"Converted to Table" = Record.ToTable(Section1),
    #"Filtered Rows" = Table.SelectRows(#"Converted to Table", each Text.StartsWith([Name], "Table1")),
    res = Table.Combine(#"Filtered Rows"[Value])
in
    res

Please mark the question solved when done and consider giving kudos if posts are helpful.

 Cheers 

[Alex_Ooi]

I had a reply from Reza, this is what he said:

"This method only works inside the Power Query Editor. the environments of #shared and #sessions are not available when you load data into Power BI."

It is such a shame the solution could work, but unfortunately it isn't yet what I want.

[AlB]

perhaps @ImkeF , @Mariusz , @edhans , @Greg_Deckler  can provide some wisdom here

[ImkeF]

Hi @Alex_Ooi ,

instead of creating single queries for each of them, create a table with one row for each of them. 

Then expand out the result column. No need to append anything.

Start from a table with parameters and add a column that grabs the data with a function.

[Alex_Ooi]

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.

[ImkeF]

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"

[Alex_Ooi]

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.

[ImkeF]

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. 

[Alex_Ooi]

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!

[ImkeF]

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..)

[Alex_Ooi]

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

[Alex_Ooi]

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.

[amitchandak]

@Alex_Ooi , refer if this can help https://radacad.com/append-vs-merge-in-power-bi-and-power-query