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

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;

input = Excel.CurrentWorkbook(){[Name="InputData"]}[Content]

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

inputrecord=Table.First(input),

Now I can read Date value easily by expression below:

date=Text.From(inputrecord[Date]),

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.

time=Text.PadStart(Text.From(inputrecord[Time]),4,"0"),

duration=Text.From(inputrecord[Duration]),

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;

WebURL="http://www.timeanddate.com/worldclock/fixedtime.html?msg=Power+BI+Training&iso="&date&"T"&time&"&p1=22&ah="&duration,

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;

Source1 = Web.Page(Web.Contents(“http://www.timeanddate.com/worldclock/fixedtime.html?msg=Power+BI+Training&iso=20160509T1300&p1=22&ah=3”)),

To this:

Source1 = Web.Page(Web.Contents(WebURL)),

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:

let

 // read Input Data

input = Excel.CurrentWorkbook(){[Name="InputData"]}[Content],

inputrecord=Table.First(input),

date=Text.From(inputrecord[Date]),

time=Text.PadStart(Text.From(inputrecord[Time]),4,"0"),

duration=Text.From(inputrecord[Duration]),

 

WebURL="http://www.timeanddate.com/worldclock/fixedtime.html?msg=Power+BI+Training&iso="&date&"T"&time&"&p1=22&ah="&duration,

Source = Web.Page(Web.Contents(WebURL)),

 

Data0 = Source{0}[Data],
    #"Changed Type" = 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}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Header"}),
    #"Replaced Value" = Table.ReplaceValue(#"Removed Columns","*","",Replacer.ReplaceText,{"Location"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","*","",Replacer.ReplaceText,{"Location2"}),
    #"Removed Columns2" = Table.RemoveColumns(#"Replaced Value1",{"Location2", "Start time2", "End time2"}),
    #"Removed Columns1" = Table.RemoveColumns(#"Replaced Value1",{"Location", "Start time", "End time"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns1",{{"Location2", "Location"}, {"Start time2", "Start time"}, {"End time2", "End time"}}),

     	
    #"Appended Query" = Table.Combine({#"Removed Columns2", #"Renamed Columns" }),

#"Grouped Rows" = Table.Group(#"Appended Query", {"Start time", "End time"}, {{"Count", each Table.RowCount(_), type number}, {"Cities", each _, type table}}),

#"Added Custom" = Table.AddColumn(#"Grouped Rows", "Cities 2", each Table.SelectColumns([Cities],{"Location"})),

#"Added Custom2" = Table.AddColumn(#"Added Custom", "Cities 4", each Table.Transpose([Cities 2])),

#"Added Custom3" = Table.AddColumn(#"Added Custom2", "Cities 5", each Table.ToList([Cities 4],

Combiner.CombineTextByDelimiter(", ")

)),

#"Expanded Cities 5" = Table.ExpandListColumn(#"Added Custom3", "Cities 5"),

#"Removed Other Columns" = Table.SelectColumns(#"Expanded Cities 5",{"Start time", "End time", "Cities 5"}),

Final = Table.RenameColumns(#"Removed Other Columns",{{"Cities 5", "Cities"}})

in

Final

 

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

 

Reza Rad on FacebookReza Rad on LinkedinReza Rad on TwitterReza Rad on Youtube
Reza Rad
Trainer, Consultant, Mentor
Reza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He has a BSc in Computer engineering; he has more than 20 years’ experience in data analysis, BI, databases, programming, and development mostly on Microsoft technologies. He is a Microsoft Data Platform MVP for 12 continuous years (from 2011 till now) for his dedication in Microsoft BI. Reza is an active blogger and co-founder of RADACAD. Reza is also co-founder and co-organizer of Difinity conference in New Zealand, Power BI Summit, and Data Insight Summit.
Reza is author of more than 14 books on Microsoft Business Intelligence, most of these books are published under Power BI category. Among these are books such as Power BI DAX Simplified, Pro Power BI Architecture, Power BI from Rookie to Rock Star, Power Query books series, Row-Level Security in Power BI and etc.
He is an International Speaker in Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday and SQL user groups. And He is a Microsoft Certified Trainer.
Reza’s passion is to help you find the best data solution, he is Data enthusiast.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.

14 thoughts on “Power Query Not for BI: Event Date and Time Scheduler – Part 3

  • Hi Reza,
    It seems Part 3 script is missing in the Full M Script provided at the end of Part 3.
    -Deepak

  • 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