Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Get data from Web with date in the link

Hello,

 

My company uses service that outputs data (KPIs) via web requests, which include list of required KPIs, line numbers and required date. I can easily transform output data into proper tables, but I need to have access to at least data from last 3 days. This should be 3 separate queries and the problem is "How to refresh recent date in URL?". For example today is 05_02_2019, I need to run 2 additional queries with 05_01_2019 and 04_30_2019 as part of URL and tomorrow I will need to run queries with 05_03_2019, 05_02_2019, 05_01_2019 and so on. Is there a way to automatically refresh this part of URL every day for each query? 

 

Also there is a option to input "Today" value instead of date in URL and I thought about running a query every day and storing all the results in some excel table, but if there is a way to keep this process in Power Bi I would like to do so.

Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

     

    This is possible. But I need more inputs. Will you be able to do the following...

     

    1. Connect your PowerBI desktop file to the web by using a static URL for the current day and import the data to a table.
    2. Then copy the PowerQuery/M Query from the Advanced Query Editor in EditQueries window and post the automatically generated power query script here.

     

    Once you post the query here, I will be able to modify it to automatically change the CurrentDate, Day-1, and Day-2 and post the modified query back to you.

     

    But the approach is as follows...

     

    1. We will have to nest three let-in statements that generate a table for each date inside an outer let-in statement 
    2. Then combine the 3 tables generated using the nested let-in statements into a single table using Table.Combine() function and return this as a result of the let-in statement at outer level.
    3. Identify the URL used for each date and modify the URL to automatically change the dates daily by using the following Power Query functions.
      1. DateTime.LocalNow() // this will get the current system date in DateTime format.
      2. DateTime.Date() // Pass the DateTime value into this to convert the DateTime value to Date value.
      3. Date.ToText() //Pass the date value to this function to convert it as a string. This will accept only Date value. So we will have to convert the DateTime from Step1 into Date in Step2
      4. Store the URL's prefix and suffix into variables. Let us call them URLPrefix and URLSuffix.
      5. Form the required URL by combining the URLPrefix, <Date>, URLSuffix where the <Date> will be the output string of Date.ToText() function. 
      6. To combine the strings, use the function Text.Combine().
      7. Now modify the power query that is generated automatically by replacing the URL in the power query script with the URL you have derived at Step 6.
      8. The steps 1 to 7 will have to be repeated for each let-in statement for Day, Day-1, and Day-2.
      9. Then combine these 3 let-in statement's output tables into a single one using Table.Combine() and return the same as the result of the outer let-in statement.

7 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

     

    This is possible. But I need more inputs. Will you be able to do the following...

     

    1. Connect your PowerBI desktop file to the web by using a static URL for the current day and import the data to a table.
    2. Then copy the PowerQuery/M Query from the Advanced Query Editor in EditQueries window and post the automatically generated power query script here.

     

    Once you post the query here, I will be able to modify it to automatically change the CurrentDate, Day-1, and Day-2 and post the modified query back to you.

     

    But the approach is as follows...

     

    1. We will have to nest three let-in statements that generate a table for each date inside an outer let-in statement 
    2. Then combine the 3 tables generated using the nested let-in statements into a single table using Table.Combine() function and return this as a result of the let-in statement at outer level.
    3. Identify the URL used for each date and modify the URL to automatically change the dates daily by using the following Power Query functions.
      1. DateTime.LocalNow() // this will get the current system date in DateTime format.
      2. DateTime.Date() // Pass the DateTime value into this to convert the DateTime value to Date value.
      3. Date.ToText() //Pass the date value to this function to convert it as a string. This will accept only Date value. So we will have to convert the DateTime from Step1 into Date in Step2
      4. Store the URL's prefix and suffix into variables. Let us call them URLPrefix and URLSuffix.
      5. Form the required URL by combining the URLPrefix, <Date>, URLSuffix where the <Date> will be the output string of Date.ToText() function. 
      6. To combine the strings, use the function Text.Combine().
      7. Now modify the power query that is generated automatically by replacing the URL in the power query script with the URL you have derived at Step 6.
      8. The steps 1 to 7 will have to be repeated for each let-in statement for Day, Day-1, and Day-2.
      9. Then combine these 3 let-in statement's output tables into a single one using Table.Combine() and return the same as the result of the outer let-in statement.
    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Thank you, but I am not able to post the link and query here due to privacy policy. Guess I`ll need to figure it out by myself with the help of your guide. Thank you for a quick and great response. 

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        All the best then...

         

        Posting a sample power query... Hope it will help you...

        let
    
    Text1 = let
    Date1 = DateTime.LocalNow(),
    Date1Text = Date.ToText(DateTime.Date(Date1),"yyyy-MM-dd"),
    URLPrefix = "ABC",
    URLSuffix  = "XYZ",
    MyURL = Text.Combine({URLPrefix,Date1Text,URLSuffix})
    
    
    in
    MyURL,

    Text2 = let
    Date1 = DateTime.LocalNow(),
    Date1Text = Date.ToText(Date.AddDays(DateTime.Date(Date1),-1),"yyyy-MM-dd"),
    URLPrefix = "ABC",
    URLSuffix  = "XYZ",
    MyURL = Text.Combine({URLPrefix,Date1Text,URLSuffix})
    in
    MyURL,

    Text3 = let
    Date1 = DateTime.LocalNow(),
    Date1Text = Date.ToText(Date.AddDays(DateTime.Date(Date1),-2),"yyyy-MM-dd"),
    URLPrefix = "ABC",
    URLSuffix  = "XYZ",
    MyURL = Text.Combine({URLPrefix,Date1Text,URLSuffix})
    in
    MyURL,

    Output = Text.Combine({Text1,Text2,Text3})
in
    Output

         

        Try pasting this into a blank query and run it. You will get some idea. Subsequently, you may replace the URL in your automatically generated power query script with the variable "MyURL" in the above example.

        For this example, I have used Text.Combine() function to merge the outputs of the three nested let-ins into a single one. In your real case, you will have to use Table.Combine(). Modify wherever necessary. 

        Also, notice the Date.AddDays function in Day-1 and Day-2 let-in statements. 

         

        All the best!

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Try adding these to your PowerQuery that fetches the data:

       

      // Get the current date in the format 'YYYY-MM-DD'
    currentDate = Text.From(DateTime.LocalNow(), "yyyy-MM-dd"),

    // Base URL without the date part
    baseUrl = "https://igateway.co.za/reports/451d723ec0etgjujbb11c3ad0dec22fb67/AllPolicies_",

    // Construct the complete URL with the current date
    completeUrl = baseUrl & currentDate & ".xlsx"