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);

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.

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).

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.

I name this as Cities 2, and the expression as

= Table.SelectColumns([Cities],{"Location"})

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;

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:

= Table.Transpose([Cities 2])

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;

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;

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

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:

=Table.ToList([Cities 4],

Combiner.CombineTextByDelimiter(", ")

)

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

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.

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

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.

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

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.