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;

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 9^th 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.

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

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.

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:

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

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.