Forum Discussion

Alex_Ooi's avatar
Alex_Ooi
Helper IV
6 years ago

Append queries dynamically in Power Query

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 q...
Need Help
Alex_Ooi's avatar
Alex_Ooi
Helper IV
6 years ago

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's avatar
ImkeF
Community Champion
6 years ago

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.