Power Query Not for BI: Event Date and Time Scheduler – Part 3

Facebooktwittergoogle_plusredditpinterestlinkedintumblrmailFacebooktwittergoogle_plusredditpinterestlinkedintumblrmail

22

Previously You have seen in Part 1 and Part 2 of this series, that how you can use Power Query to do data transformation and turn the result of event date/time scheduler of timeanddate.com website to a better format. We’ve used number of basic transformations, and some functions such as Table.Transpose to fetch desired output. This part is the last step which we will use parameters to make our query dynamic. At the end of this part you will learn how to automate your Power Query solution, with a change in parameters in an Excel table, and hit refresh button a new result set with desired format will be created. To learn more about Power Query, read my Power BI online book; Power BI from Rookie to Rock Star.

 

Using Parameters

We’ve made all the data manipulation and mash up, and the output result set is exactly as desired. However, one step is left: Using Parameters and making this query dynamic or automate the process. For parameters I use an Excel table as my configuration table.  So I create a table as below;

021

This table has three columns: Date, Time, and Duration. I separated the date and time for simplicity of this example. Date to be formatted as YYYYMMDD, and Time as HHMM, and duration as an integer value illustrating hours.

Configuration above means the event starts at 9th of May 2016, at 1:00 pm New Zealand time (this is what my local time is), with duration of 3 hours. I named this table as InputData.

I can fetch this table in my Query Editor with a simple M script line as below;

Excel.CurrentWorkbook reads a table from the current excel workbook. Name of the table passed as input parameter. And [Content] will load content field of the table (which is a table by itself of all columns and rows of the Excel table) into the variable named input.

22

I can then read first record of this table into a variable called inputrecord. And use it for fetching each parameter later on. Here is my inputrecord

23

Now I can read Date value easily by expression below:

For Time I do also use a PadStart with 0 to generate values like 0400 when time is 4AM. The reason is that TimeandDate.com asks for a full HHMM string with leading 0 to work correctly. Duration calculation is simple as well.

Now that I have all three parameters in variables named; date, time, and duration. I can simply use them in generating web url to pass to timeanddate.com website as below;

String concatenation character in Power Query is: &. and expression above generates the web url for the date/time of event with local time of Auckland, New Zealand with duration specified. All I have to do now is to fetch data from this URL with the WebURL variable. So I’ll change line below;

To this:

Now I have a dynamic query based on parameters. Let’s change the parameters in Excel and Refresh the data.

025

When I refresh the data I can see in the status bar that Power Query is refreshing the whole data set based on input parameters (illustrated in screenshot above).

And here is my final result:

026

 

Here is the full script of example above:

 

Summary

You have seen how Power Query can be useful for even none BI use case scenarios. You’ve learned how to use Power Query to get input parameters dynamically from Excel spreadsheet and call a web URL with that, fetches the data set, and do data transformations. This was a real world use case that showed you some strengths of Power Query. No matter if you are a BI developer or not, you will find use cases of Power Query in many situations.

 

Call to Action

Tell me real use case scenarios that you have used Power Query for, how did you find Power Query in solving that challenge?

 

Previous Steps:

Power Query Not for BI; Event Date and Time Scheduler – Part 1

Power Query Not for BI; Event Date and Time Scheduler – Part 2

 

Facebooktwittergoogle_plusredditpinterestlinkedintumblrmailFacebooktwittergoogle_plusredditpinterestlinkedintumblrmail
rssyoutuberssyoutube
Reza Rad
Reza Rad is an Author, Trainer, Speaker and DW/BI Consultant. He has a BSc in Computer engineering; he has more than 15 years’ experience in databases, programming and development mostly on Microsoft technologies. He is a Microsoft MVP in Data Platform for seven continues years (from 2011 till now) for his dedication in Microsoft BI. He is author of some SQL Server and BI books, and also Power BI online book; from Rookie to Rock Star.

14 Comments

  • Hi Reza,
    If I run your M script as it is, I get the error, “Duplicate Initializer found” for Renamed Columns because it has been already used earlier, therefore, I changed the same to “Renamed Columns 1″. After fixing this, I’m getting an Unexpected Error, ” An item with the same key has already been added”. Not able to debug the same. Thanks.
    -Deepak

  • Hi Reza,
    The Script is not running, if I run it as it is by creating the specified Input Data Table and pasting the Script as it is (after fixing the Renamed Columns issue as mentioned in my earlier reply). I get the following error: “The Column ‘Header’ of the table was not found”. If I click on “Go to error”, it takes me to the following portion of the script
    = Table.TransformColumnTypes(Data0,{{“Header”, type text}, {“Location”, type text}, {“Start time”, type text}, {“End time”, type text}, {“Location2″, type text}, {“Start time2″, type text}, {“End time2″, type text}})

    Not able to debug the same. Need your help. Have spent close to 2.5 hours analyzing same.
    Thanks.
    -Deepak

    • Hi Deepak,

      You are right. that was because of the name of latest step which is similar to another renamed column step earlier. I updated the script and renamed the latest step to final. Now it should be working.

      Cheers,
      Reza

        • Hi Nasser, All Parameters, Custom functions, and the rest Power Query experience would be exactly the same in Power BI Desktop and Excel. UI is different in few places, but the code behind is always the same.
          you can use a source excel file for configuration, and do the rest in Power BI Desktop

  • Hi Reza,
    I got the following error message during the step “Source = Web.Page(Web.Contents(WebURL))”
    “Query is accessing data sources that have privacy levels which cannot be used together.”
    Could you please explain for me what’s the problem and how to solve it. Thanks!

    • Hi Julian,
      This happens when you are working with some data sources that are coming from web, or excel files, or some sources that might not be trustworthy.
      You can skip this extra validation by going to File, Options in Power BI Desktop, and under Global, Go to Privacy tab, and set “Always ignore privacy level settings”
      Cheers
      Reza

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">