How to Pass Parameter Value from Query 1 column to create Query2

[Anonymous]

I have been receiving email(on daily basis) which contains "https://server.com/phid?=dynamicID". If I click the URL from my mailbox, It will download the csv file from thrid party server. I could able to use this URL to create new Power BI report and downlad the data into Power BI Desktop. But how to automate this. I tried using parameter but it did not work as expected. If I downoad email body as text file and create Query1. How can Pass the URL into webbased query2. Please share any idea would be helpful. 

Regards,

PV

[v-lid-msft · Accepted solution]

Hi @Anonymous ,

First of all, we need to Convert the text body of email into the url:

let
    Source = "This is the body of email, please download the csv file from this link: https://server.com/phid?=dynamicID .this is the end of email",
    GetStartPosition = Text.PositionOf(Source,"https://server.com/phid?"),
    DeleteStart = Text.End(Source,Text.Length(Source) - GetStartPosition),
    GetEndPosition = Text.PositionOf(DeleteStart," "),
    DeleteEnd = Text.Start(DeleteStart,GetEndPosition)
    
in
    DeleteEnd

Then we can use the Query1 inside the Web.Contents to get the csv file and convert it into table.

let
    Source = Web.Contents(Query1)
in
    Source

Best regards,

[v-lid-msft · Accepted solution]

Hi @Anonymous ,

First of all, we need to Convert the text body of email into the url:

let
    Source = "This is the body of email, please download the csv file from this link: https://server.com/phid?=dynamicID .this is the end of email",
    GetStartPosition = Text.PositionOf(Source,"https://server.com/phid?"),
    DeleteStart = Text.End(Source,Text.Length(Source) - GetStartPosition),
    GetEndPosition = Text.PositionOf(DeleteStart," "),
    DeleteEnd = Text.Start(DeleteStart,GetEndPosition)
    
in
    DeleteEnd

Then we can use the Query1 inside the Web.Contents to get the csv file and convert it into table.

let
    Source = Web.Contents(Query1)
in
    Source

Best regards,

[Anonymous · Accepted solution]

Hi Li, 

Thank you so much for solution. A small change I did.

As I have mentioned I have my text file which has uRL. This is my first query. and then I used this in 2nd query as your 2nd query.

Worked like magic! Appreciate your help!

******************

let
Source = Csv.Document(File.Contents("C:\Users\user\\folder\Mail.txt"),[Delimiter=",", Columns=8, Encoding=1252, QuoteStyle=QuoteStyle.None]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}}),
#"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Column1] = "https://server.com/fo/report/get_scheduled_report.php?id=xyzerer13ef3c6c7cd1fdfddfdfd2f8ece814a0c8c8...")),
#"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Column1"}),
Column2 = #"Removed Other Columns"{0}[Column1]
in
Column2

[v-lid-msft]

Hi @Anonymous ,

Glad to hear that you have resolved your problem. But we suggest you to delete the url information in your reply if it is real link.

Best regards,

[Anonymous]

Hi Li,

Do you already know this above approach(Web.Contents) Function doesnot support Power BI service scheduling feature ? Then what is the workaround to share this report with end users. Please let me know except manul refresh process using power bi desktop.

Regards,

Pulendar

[v-lid-msft]

Hi @Anonymous ,

Sorry for that we forget to mention that we need to change the parameter of Web.Content function if the url is dynamic, Please try to use the following query:

let
    Source = Web.Contents("https://server.com",[RelativePath=Text.Replace(Query1,"https://server.com","")])
in
    Source

Best regards,

[Anonymous]

If my URL is below 

https://myacutalserver.com/abc/dec/egh_report.php?id=asdf7f7g7s7g7s7g7w3343dfdfd7770g9fdfd99dfd

''asdf7f7g7s7g7s7g7w3343dfdfd7770g9fdfd99dfd' is dynamic.

Should I write as below :

let
Source = Web.Contents("https://myacutalserver.com",[RelativePath=Text.Replace(URL_Query,"https://myacutalserver.com","")])
in
Source

But the above code didn't work. Am I missing something.

[v-lid-msft]

Hi @Anonymous ,

For example, the result of Query is similar as following:

Then the Query2 can be following

let
Source = Web.Contents("https://www.bing.com", [RelativePath=Text.Replace(Query1,"https://www.bing.com","")])
in
Source

Then we use Web.Page to get some table for test.

After publish it to service, we can configure the schedule refresh 

Best regards,

[Anonymous]

Hi Li,

Sorry for late response. I have gone through your solution to fix shceduling. Which will help if the data appear as html table on webpage. But my schenario is, if put my complete url into browser, it just donwload csv file. In otherway it just url of csv file.

That's why now I am looking for options to refersh Power BI desktop using CMD and saving it to Onedrive. Poiting that file to Power BI online.

Thank you for your help!

[Anonymous]

Thank you! That's Dummy URL!