question

robertopogliani-6622 avatar image
0 Votes"
robertopogliani-6622 asked robertopogliani-1537 edited

Power Query execute command

Hello,
In Power Query, is it possible to execute a URL command before starting to download the data set?
Example:
I need to execute www.mysite.com/refresh-csv.php
before getting data with
Source = Csv.Document(Web.Contents("www.mysite.com/data.csv") .............
Thanks
Roberto

power-query-not-supported
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Ehren avatar image
1 Vote"
Ehren answered Ehren commented

Yes, with some caveats. You should be able to create a query that downloads the content from the first URL (Query1), then, in the query that gets the second URL (Query2), reference the results of Query1 before downloading the second URL. For example, create a dummy "if" expression: if Table.RowCount(Query1) > 0 then Csv.Document(Web.Contents(".../data.csv")) else null

However, this approach comes with a caveat: Power Query isn't designed to trigger operations that have side-effects. For example, when working with web data PQ will cache the results of web pages instead of downloading them afresh every time. This could cause you to experience instances where the first URL isn't actually re-accessed, even though the second URL is. During a refresh operation, the cache is initialized to an empty state, so these behaviors shouldn't be as much of an issue there. But in the Power Query Editor, caching will definitely affect whether or not the pages are downloaded.

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi Ehren,
your solution is smart, but I do not think it is applicable to my project.

I have a php script, it starts when I call it by URL (for example: www.mysite.com/refresh.php).
If I download the output of the php script (csv or db table), the script is not triggered.

In other words, I could just put the link www.mysite.com/refresh.php in a cell of my excel sheet and click there before refreshing the power query; but my target was to get all with a single click.
I thought it was possible to execute a link with some command inside the power query.
Thanks
Roberto

0 Votes 0 ·
Ehren avatar image Ehren robertopogliani-6622 ·

Why is it not applicable? The basic idea is that you can write a PQ query that accesses refresh.php before accessing data.csv.

0 Votes 0 ·
robertopogliani-1537 avatar image
0 Votes"
robertopogliani-1537 answered Ehren commented

Hi @Ehren,
I wish to come back over this topic to understand better the possibile solution.
I need to execute a php script before downloading a csv file.
I understand that every time I want to use fresh data from PowerQuery I need to click the button Refresh, but I want also to execute a script before using the csv content.
Making an example:
Query1 >>> www.mysite.com/refresh-csv.php
Query2 >>> Source = Csv.Document(Web.Contents("www.mysite.com/data.csv") .............

I do not understand how to use the tip suggested:
if Table.RowCount(Query1) > 0 then Csv.Document(Web.Contents(".../data.csv")) else null

My doubt is that the output of my Query1 is not a table, but it creates the csv file that have to be used when the IF clause is True.
I have seen that there are M functions for Csv.Document as well, but I am not able to find the right solution.

Thanks
Roberto

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Can you share the actual text of your queries (replacing the actual urls if you prefer to keep them private)? You can select multiple queries in the PQ Editor by holding down Shift or Ctrl and clicking them, then copy them and paste the text of all the queries into a reply here.

0 Votes 0 ·
robertopogliani-1537 avatar image
0 Votes"
robertopogliani-1537 answered Ehren commented

Hi @Ehren,
My target is:
when I press the Refresh button, the file data.csv should be refreshed before downloading, therefore I need to execute the script www.mysite.com/refresh-csv.php, it takes max 2-3 seconds.
With excel I could make that with a macro, but with "PowerBI" I think I need to find a solution inside PQ.

Query1 (Now I execute it manually in the browser)
www.mysite.com/refresh-csv.php

Query2 (made with PQ tools)
let
Source = Csv.Document(Web.Contents(""www.mysite.com/data.csv"),[Delimiter=";", Columns=35, Encoding=65001, QuoteStyle=QuoteStyle.None]),
#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=false]),
#"Modificato tipo" = Table.TransformColumnTypes(#"Promoted Headers",{ {"id_order", Int64.Type}, {"qtaOrd", Int64.Type}, {"anno", Int64.Type}, {"date_add", type datetime}, {"cambioPS", type number}, {"cBanca", type number}, {"valuta_total_paid", type number}, {"eur_total_paid_tax_incl", type number}, {"total_paid_real", type number}, {"eur_total_paid_imponibile", type number},{"eur_total_shipping_imponibile", type number}, {"eur_merce_imponibile", type number}}, "en-US"),
#"Removed Columns" = Table.RemoveColumns(#"Modificato tipo",{"cli_fat", "city_fat", "prid_fat", "pr_fat", "prov_fat", "nazid_fat", "naz_fat", "prid_spe", "pr_spe", "nazid_spe"}),
#"Inserted Day Name" = Table.AddColumn(#"Removed Columns", "GioSett", each Date.DayOfWeekName([date_add]), type text),
#"Riordinate colonne" = Table.ReorderColumns(#"Inserted Day Name",{"id_order", "qtaOrd", "anno", "GioSett", "date_add", "gruppo", "negozio", "reference", "stato_ordine", "cli_spe", "vettore", "shipping_number", "city_spe", "prov_spe", "naz_spe", "ivaUE", "area", "valuta", "cambioPS", "cBanca", "valuta_total_paid", "eur_total_paid_tax_incl", "total_paid_real", "eur_total_paid_imponibile", "eur_total_shipping_imponibile", "eur_merce_imponibile"})
in
#"Riordinate colonne"

Thank you in advance for your help
Roberto

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Change Query1 to something like:

Web.Contents("www.mysite.com/refresh-csv.php")

Then set Query1 and Query2 to be unloaded (so they won't refresh on their own) and create a third query that pulls on Query1 before returning Query2. Something like:

if Binary.Length(Binary.Buffer(Query1)) <> null then Query2 else null

1 Vote 1 ·
robertopogliani-1537 avatar image
0 Votes"
robertopogliani-1537 answered robertopogliani-1537 edited

Hi @Ehren,
I feel the solution is near, but there is something still missing
See attachment
Thanks for your help
Roberto

212625-immagine-2022-06-18-102511.png



5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

robertopogliani-1537 avatar image
1 Vote"
robertopogliani-1537 answered robertopogliani-1537 edited

Hi @Ehren,
New update. I have fixed my issue.

Because of the M syntax, the numbers in front of a query name should be written in a special way.
I simply renamed the queries and everything is working.
Thank you.
Roberto

212685-image2022.png



image2022.png (17.6 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.