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

Facebooktwittergoogle_plusredditpinterestlinkedintumblrmailFacebooktwittergoogle_plusredditpinterestlinkedintumblrmail

020

In first part of this series I’ve explained that Power Query can be used for non-BI scenarios, and here is a real-world use case example; Creating an event date and time scheduler which consider multiple time zones. In previous post you’ve learned how to use Power Query to do some data mash up to build a unified list of cities in different time zones and start and end time of event in each city. In this post we will continue steps to build the final result set. If you want to learn more about Power Query read my Power BI online book; Power BI from Rookie to Rock Star.

 

Group Rows

After creating the full list, I want to group rows by their start and end time. I want to create groups of cities where start time of the event is the same (and definitely end time would be the same).

I start by clicking on Start time and End time columns and then I choose Group By from the Home menu under Transform. In Group By window I can see that Start time and End time already selected as Group By columns (I can add or remove from this window by clicking on – or + buttons if I want to).

I create two new columns for group by result set, one as a count of all rows in each group (this is just out of curiosity to see how many cities I have in each start/end time group), and the other one as all rows for each group (this will generate a table for each group, containing all cities of that group; all cities with same start/end time);

011

Output of this step will be a table with unique start/end times plus two new columns; count of all rows, and a table embedded column for list of all cities.

012

I can click on a cell in the Cities column to see contents of it (Do not click on the Table word, click on an empty space in that cell. If you click on the Table word a new subsequent step will be generated to fetch that specific table).

013

Concatenate Rows

Content of each cell in above screenshot is a table containing all cities in that group. For example, for the selected cell above I have a table of 22 cities.

So now my next step is to create a string concatenated list of all these city names, like: Addis Ababa, Amman, Ankara, Antananarivo, Athens…

I can transform this column to be concatenated list all in one, but for simplicity and ease of understanding I’ll do that step by step by adding a custom column in each step;

Step 1 to Concatenate: Select Single Column

As the first step to concatenate, I’ll just fetch single column from the sub-table which is Location .

I create a new custom column by click on the Add Column menu, and then Add Custom Column icon. This will open Add Custom Column window for me where I can define a new column with the name and the expression to calculate the new column.

014

I name this as Cities 2, and the expression as

Table.SelectColumns only fetches specific columns of the table that we mention in the {}. Here I only fetched Location column, so it will be a single column table as a result;

015

Step 2 to Concatenate: Rows to Columns

For concatenation I want to use a method that needs values to be on COLUMNS not on rows. So I have to transform the inner table to show values in Columns rather than rows. Fortunately it is easy to do with Table.Transpose. I add a new custom column with this expression:

This simple expression will turn my single-column row values into multiple columns in a single row. For using Transpose All you need is table name. Table should be single columned. And then it transforms it to rows. Column headers would be auto named as Column1, Column2,…. So this would be the result;

016

A bit of code behind

You might wonder what is happening when you add a custom column which is based on an expression which uses an existing column. With a glance at Advanced Editor you can see this is the script line generated for adding this custom column;

Table.AddColumn adds a new column with name and expression. This function gets three parameters;

  • Name of the table to add column to it: #”Added Custom”
  • Name of the new column to generate: Cities 4
  • Expression to populate the new column: each Table.Transpose([Cities 2])

EACH is a singleton function that applies on every row of the main table, and produces the result of expression for that row into the new column.

Step 3 to Concatenate: Concatenate to List

Now that I have values in multiple columns I can concatenate them all into one string with Table.ToList function which converts a table to List. This function can concatenate all columns of table into one column (because List is a single columned data structure).

The actual concatenation happens by Combiner function; Combiner.CombineTextByDelimiter(“, “) which concatenate values with a delimiter which I set to be comma. So here is the expression for my new custom column:

And the result would be a list which contains concatenated string value;

017

Step 4 to Concatenate: Expand

All I have to now is to expand the list to its values. Expanding a column means expanding its underneath structure. Expand column appears when you have a multi value structure in each cell. Multi-value structures in Power Query can be; Table, List, and Record. You can find Expand Column Icon simply besides the column heading and click on it.

018

After expanding I can see concatenated text values in my main table as below;

019

Final Polish

Now I have Start/End time for each time zone, and concatenated list of all cities in that time zone, so I just do a bit of polishing with removing extra columns. Only keeping Start Time, End Time, and Cities 5. And renaming Cities 5 to Cities.

020

 

Next Steps

I’ve made my result set as I want with Power Query so far, but still one step left, which is using parameters and Automation; I’ll be using an Excel table as input parameters to set my local event date and time, and then Power Query will use that input parameters to refresh the whole result set. Stay tuned for the next post to see how it can be done.

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

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

 

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.

5 Comments

  • Very nice tutorial! Isn’t it simply brilliant who easy Power Query makes it to create temporary tables whose results can even be watched and checked during design?

    One little tip for improvement: If you choose this command in Step 1:
    = [Cities][Location] instead of this: = Table.SelectColumns([Cities],{“Location”})
    the result will be a list instead of a table with one column. This would make Step 2 obsolete, so you can right go to step 3 (and skip the “Table.ToList”-element in it as well) ;-)

    • Thanks Imke,
      Good to see you here :)
      Brilliant.Of course, great suggestion. Thanks for this. I appreciate it. Much better approach for doing this.

      Cheers,
      Reza

      • Hi Reza,
        I was trying out Imke’s trick of directly getting List instead of Table. Following is my M Script:
        #”Added Custom” = Table.AddColumn(#”Grouped Rows”, “CitiesList”, each [Cities][Location]),
        #”Added Custom1″ = Table.AddColumn(#”Added Custom”, “CitiesListConcatenated”, Combiner.CombineTextByDelimiter(“, “))

        I’m getting error while executing the script. Would be great if you or Imke can help me out.
        -Deepak

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="">