Categories
Date - Time

Dynamic Calendar Table

How to create a Dynamic Calendar Table

Author: Skillwave Training

The Scenario

When you are facing a scenario where you need to create a Dynamic Calendar Table (or Date Dimension Table), we take in consideration that we could create such table in Excel and use it later in our Time Intelligence Scenarios with Power Query or Power Pivot.

Most of the time, this Date dimension would be too long and would contain dates that are not relevant to the current report. (Like dates from the year 2020 for example and we only have data until the year 2015; that translates into 5 years of emptiness or waste in our report)

There are other scenarios where you simply want a nice calendar table in with text labels or other formats using your specific culture (Spanish, Portuguese & such).

This Pattern will try to show you how to create a Calendar Table, how to use some of the Date functions found in Excel, and how easy it is to work with Power Query.

Download the sample file

Step 1: Create the Query and Add a Start Date

Before we begin adding any type of code, we need to create the query. For that, we’ll go to Power Query ->  Other Sources -> Blank Query.
That will launch the Power Query Editor and within that window we can add our own code in the formula bar. Inside that bar we add the following code:
=#date(2013,1,1)
DateLiteral
You’ll notice that we just added something called a literal by using #date(2013,1,1). What we did is simple – we added a date value. 2013 is the year value, the second parameter is for the month and the last parameter is for the day.

We added that in our first step. The name of that step is Source and it holds the value of the start date of our calendar table.

Step 2: The Code That Does the Magic

Let’s go ahead and create a new custom step by clicking on the fX icon right in the formula bar. This will add a new step called Custom which would reference to the previous step. We would like to change that original code in the formula bar to be this one:
= List.Dates(Source, Number.From(DateTime.LocalNow())- Number.From(Source) ,#duration(1,0,0,0))  

Here’s the breakdown of that formula:

  • List.Dates – it helps us a create a list of dates and its inputs are
    • A Start Date
    • The number of values to return
    • The increment to add
  • Source – as you already know, this is the value of our Start Date so it goes inside the first parameter
  • Number.From – we use this function multiple times to transform any type of value into a Number and use it for math operations
  • DateTime.LocalNow – this is the equivalent of NOW() in Power Query so it returns the current date and time. This will make sure that we only get a dynamic range of dates until the present day
  • #duration(1,0,0,0) – another literal that adds 1 day (so that our increment is on a daily basis)

This formula returns a List of Dates as you can see in the next image:
FormulaMagic
Go ahead and convert that list into a table using the List Tools ribbon. After you convert it into a table, please change the data type of that column to be Date and rename that column to something more descriptive like Date.

Step 3: Add the Date/Number-Based Columns (Using the UI)

Next step is to add the much needed columns such as Month Number, Year, Start of the Month Date, End of the Month Date and all that good stuff.

We do that by simply following these steps (please make sure that you select the Date Column):

  1. Go to the Add Column ribbon
  2. Go to the From Date & Time Group
  3. Select the new column that you want from the dropdown

Here’s how the result would look like:
DateUI

Step 4: Add the Text-Based Columns (and the Culture Option)

This is the last step and we’ll add some new custom columns by going to Add Column -> Add Custom Column.

Next, we’ll add these new columns by inputting the formulas to the Add Custom Column window:

  • Month Label Column = Date.ToText([Date], "MMM", "es-419")
  • WeekDay Label Column = Date.ToText([Date], "ddd", "es-419")

In that last parameter we used the text value “es-419” so we can get the labels in Spanish.

You can use the following link to see the National Language Support (NLS) API Reference and find other cultures. This last parameter is optional and if you don’t use it then all will be calculated using the English (en-US) language.

The Final Result of this could look like this:
FinalTable

Conclusion

We now know how to create a simple Dynamic Date Dimension or Calendar table and how the UI works in order to create some new date columns. This truly shows how extensible and easy to use Power Query is.

Categories
Combine

Combine or Append Data from Files

Combine or Append Data from Files

Author: Skillwave Training

The Scenario

If you ever need to:

  • Append Data,
  • Consolidate Data, or
  • Combine Data

that comes from either an Excel, text, or CSV file, then this is the best solution for you to do so.

In this pattern you’ll get the most optimal and easiest way to combine your files from an specific folder and combine them all together if you’d like. That’s right! Combine data from text, CSV, and Excel files all together. There are no limits on the size of the file or how many files you’d like to combine – it’ll simply work!

Download the sample file

Our Goal

AppendGoalWhat we need is a way to extract all the data from our files like:

  • CSV Files
  • Text Files
  • Excel Files

and then somehow consolidate or append data in one tall table. This was a rather complex scenario that we could solve with VBA or SQL, but now we have a more efficient and user friendly way of doing this. Don’t forget to download the workbook in order to follow along!

Step 1: Unzip the downloaded file and explore!

Before we begin with the real pattern, make sure that you download the workbook in order to follow along. The workbook comes with the files that you can see in the previous image inside a folder called PQExample which is basically the folder where we’re going to point our Power Query solution to work on. The file that contains the actual Power Query solution is called Ultimate Combination.xlsx and you should be able to see it immediately after you unzip the downloaded file.

We encourage you to explore all files and see what’s in it. Overall, they all share the same column header names:

  • Product
  • Date
  • Gross Sales
  • Amount

Now we can head over to the real Pattern.

Step 2: Find the Query that does the Magic

Once you open the Ultimate Combination.xlsx file, you’ll notice that it has no data. The Power Query solution has been stored as a connection only and its awaiting your command to load its data to your Excel workbook. In order to view this query, you’ll need to go to the Power Query ribbon, click on the Show Pane icon and then on the right side you’ll see the Query Pane with a query called Ultimate Combination. Right click that query and then click on Edit to open the Query Editor Window and analyze the solution.
QueryError
You’ll immediately notice that there is an error with the query, but don’t be alarmed. The reason behind such error its because the solution is pointing at my (Miguel) local folder instead of yours. In order to fix that you’ll need to head over to the first step called Source and click on the gear icon right next to it. That should pop up a new window with a user friendly folder browser. Go ahead and find the folder you just unzipped called PQExample.
FromFolderAnd once you do that, you’ll notice that the solution will start to run and do its magic.

You could end it here and call it a day since all the files were combined already (click on the step called Expanded to see the final result), but instead, we ‘re going to show how simple creating a solution like this was.

Understanding the Query

Instead of writing a long paragraph, we’ve divided this into sections so you can understand each step of the query on its own. You can click on the toggles to expand and view the content and collapse it back if you want.

Source

The way that we start the query is by selecting the folder where all my files are stored. This is easily done through the Power Query Ribbon as shown in the next picture:
FromFolderPQone thing to take in consideration is that Power Query will also grab the files from any subfolder, but you can filter those out by using the Path field.

Get the files

In this step we simply filter the file extension file so we only get the following extensions:

  • extension equals .csv
  • extension equals .txt
  • extension begins with .xls

The way that you’d do this is by selection the filter icon from the field and simply do a filter like you’d normally do in Excel.FilterField

Remove Other Columns

In this case, we’ll be removing some columns that we don’t need, but instead of selecting the columns that we don’t want we’ll be selecting the ones that we want and tell Power Query that we just want to keep those. Take a look at the following picture to find the button that does the trick but make sure that you first select the columns that you want to keep.
RemoveOtherColumns

Trans1

Here’s where we’ll need to get to know a bit about Power Query functions. We know that we have some Excel files in our query but, how do we extract the data from them?

Take a look at the columns that we have available. You’ll notice that we have a column called Content that holds a binary. That binary is the actual Excel file and in order to interpret that binary we need a function called Excel.Workbook(). 
ExtractWorkbook
Using the following formula:
if Text.StartsWith([Extension], ".xls") then Excel.Workbook( [Content]) else null

we get a new column that is basically the one that shows me all the data that the Excel file (on each row) holds.
You can click on any of the Table values found on the Custom column to find out what’s inside of each of them.
More often, we deal with 3 different kinds of Data inside an Excel Workbook:

  1. A Sheet
  2. A Table
  3. A Named Range

Note: You’ll notice that in some files we’ll have tables, sheets and in others we’ll only have sheets. This is the moment where we define if we just want to combine the tables, the sheets, the named ranges or a combination of them. Be sure to check that you’re not combining the same data twice as a table is part of a sheet and could potentially get combined in the wrong way.

Trans2

Now we need to expand that Custom column so we can get all the Excel data in our Query and choose the ones that we want.
ExpandExcelThe result of that gives us 4 new columns:

  • Custom.Data = the actual data found inside the Excel workbook (represented as a table)
  • Custom.Name = the name of the worksheet where the data is stored
  • Custom.Item = this is the name of the item, if it’s a named Range then it’ll be the name of that range, for Sheets then it’ll be the name of the Sheet and for Tables it’ll be the name of the table
  • Custom.Kind = it’s the name of the object that was found from the excel workbook. Most commons are Sheet, Table and DefinedName

The Formula and Other Steps

So far we managed to extract the data from the Excel workbook into objects. Now we need to find a way to extract the data from the CSV and TXT file and being able to combine that with the data found in the Excel file.
We’ll create a new column with this formula that will do the trick:

if [Extension] = ".csv" then
Table.PromoteHeaders(Csv.Document([Content]))
else if [Extension] = ".txt"
then Table.PromoteHeaders(Csv.Document([Content],null,"," ))
else if [Custom.Kind] <> "Table"
then Table.PromoteHeaders([Custom.Data])
else
[Custom.Data]

Note: we are assuming that the txt file is delimited by a comma, but you can change that by changing the comma in this line of code for something else like a pipe (|), bars (/), semicolon or other.
Csv.Document([Content],null,"," ))

The result of that formula gives us a column that holds only tables with the correct headers. You might be tempted to expand this column right now, but we won’t do that just yet. Instead, we’ll clean the table that we currently have and delete unnecessary columns like Content and Custom.Data since all the data that I need is stored on the Custom column. We will also add a new column that will give us some important information, total row count, about the tables that we’re about to combine. Using this formula we get the total row count for those tables:
Table.RowCount([Custom])

And this is what you see at the Almost there step.
Almost there

Dynamically Get a List of All Headers in Files

Fundamentally, that’s what the formula at the MyList step does. It basically grabs all the headers from all the tables found in the previous step, gathers them in a table and then creates a table of distinct values. It later transform that Table into a List as we’ll need that List later. That list will become a parameter so we can dynamically combine all the files regardless if they have all the same structure or not.

A common use case to do something like this is that perhaps we have some columns in some files but that are not present in others, but those columns might be needed for that specific analysis and this MyList pattern does just that. It dynamically creates a unique list of all the headers found thorough all the tables in the previous step.

Here’s the code:

=List.Distinct( 
    List.Combine( 
       List.Transform(#"Almost there"[Custom], 
       Table.ColumnNames)
                ) 
  ) 

In order to insert this code you’ll need to create a custom step. You can create a custom step by clicking on the fx icon in the formula bar. Once you activate that custom step, you’ll have to paste the code above to make it work.

This is a custom code that:

  1. Converts the column of the previous table that has the “Table” values into a list with just table values
  2. What List.Transform does it that it transforms that list into a List of Lists where each list inside that List contains the column names of a specific table
  3. List.Combine does just that – it combines all of the lists into a single single
  4. List.Distinct is a function that simply remove the duplicated values from a list in order to keep unique values.

This is BY FAR the most efficient way to extract all of the column names from a list of tables. We need this specific list of column names for the final steps.

The Final Steps

The 2 final steps:

  • Here we go
  • Expanded

The Here we go step simply does some renaming on the columns by just double clicking the name of those columns and the Expanded just simply expands the Custom column by simply clicking on the opposite arrows icon next to the name of that column.

The result of this solution / query should look like this:
FinalStep
Load To
All you have to do now is select where you want to load this, either to a Worksheet or your Data Model!

Categories
Combine

Merge Tables

Merging Tables

Author: Skillwave

The Scenario

If you’ve worked with Excel for any length of time, it’s almost certain that you’ve ended up with one table that has your base information, and a few more tables that contain related records. We call those other tables “lookup” or “reference” tables as they contain the missing information that we often want to pull into our main data table, allowing us to make better use of Excel’s rich toolsets. This process is commonly called enriching a table or de-normalizing a table, and is solved by most Excel users via a combination of formulas including:

  • VLOOKUP,
  • HLOOKUP, or
  • a combination of INDEX/MATCH functions.

This time, we’re going to create a dynamic process with Power Query that runs faster and is more intuitive than VLOOKUP. So prepare to say goodbye to the VLOOKUP era and say hello to Power Query for Excel

Download the sample file

Our Goals

We have three single tables that we want to merge together:

  1. An Orders table containing the products that were purchased in each sale, as well as the date and the price of the components that were sold,
  2. A Products table containing a list of products and their attributes, and
  3. A Customer table containing information about each unique customer.

Our goal is to merge all three tables using the Orders table as our base, adding the related records and attributes from the other two tables to each row from the Orders table. Our end result will be one large table with all the related columns housed together.

Step 1: Get the Data into Power Query

Our data is already highly organized for us, with all tables defined as tables in Excel. So our next step is to take advantage of that, and create pointers to those tables inside Power Query.
In order to reference those tables in Power Query we:

  1. Click any cell in the Orders table
  2. Select the Power Query tab
  3. Click on the From Table button
  4. Once the Power Query Editor window pops up, click the bottom half of the Close & Load button, then choose Close & Load To…
  5. Now we get a new window that will let us choose where to load the Data. Select Only Create Connection and click OK
  6. Repeat steps 1-5 for the Products and Customers tables

StepsToLoad

Step 2: Merging Magic – Merge Tables Together!

For this second part all we have to do is reference our base table (or query) and in this case we’re going to use our Orders table as our base.

We go to the Power Query tab and select Show Pane so we can see the Query Pane on our right hand. Then we right click on the Orders query and select Reference.
QueryReference

This essentially just pulls the results of the first query into a completely new query, which you’ll see in the Query Pane as Orders (2). Right click that new query and choose Edit to start the merging process.
Once you’re in the Power Query Editor Window, click the Merge button. This will pop up a new window that will guide us through the process of merging our tables together.

We need to define which table to merge with our current table and which columns contain the info to be matched.

This is the tricky part of the merge. Like working with VLOOKUP, we are trying to take a table that has a column with many values in it, and look up those values in a column that has a unique list of values. It’s important that your base query starts as the “many” side of the relationship, and that your “lookup” column is the “one” side of the relationship.

In this case our Orders table has many products in it, but our Products table has a unique list of products. So in the bottom portion of the window, we’ll choose to merge the Products table with our base table (Orders).

Next we need to identify the column that is common between the two tables (ProductID in this case). Then we simply select the ProductID columns in both tables so that they are highlighted, and click OK.
Merge example

Repeat the same process to Merge the Customer table on the CustomerID column as shown in the picture above. The result should look a bit like this:
MergedTables

As you can see from the picture above, we added two columns containing the word Table in a green font. But it’s more than just a word… these are actually truly tables that contain each row of data from the corresponding rows of the Customer and Products Table!

We can peek inside by clicking the white space beside the word table, or we can expand those tables so we can get the columns from each.

Now that we know what’s inside of those table values for each row, let’s expand them into columns. Simply click on the directional arrows icon next to the name of the column to Expand the column, as shown in the following image:
Expand

From the picture above, you can see that you are able to choose which columns you want to keep, and which you don’t want. (Un-check the box that states Use original column name as prefix, or you’ll get columns named NewColumn.ProductID instead of just ProductID!)

Expand the other column as well – and don’t forget to un-check the Use original column name as prefix box again if it’s checked.

The final step of our cleanup is to force the Date column’s data type. Select the Date column, go to the Home tab, and set the data type to Date:

ChangeDataType

 

Step 3: Load it to Excel!

And that’s it! All you have to do now is go to the Home tab, click Close & Load and choose where to land your output (an Excel Worksheet or the Data Model.)

Check the Results Worksheet in the sample file to see the output of the query as constructed in this pattern.
FInalResultMerge

Categories
Summarize

Grouping or Summarizing Data

Grouping or Summarizing Data

Author: Skillwave

The Scenario

One of the most common request of an Excel pro is to group and summarize data. This pattern shows you how to create a compelling report from just a single source of data, which can be refreshed at any time with a single click.

The scenario uses a simple sales table which includes a listing of all products (t-shirts) sold, the date of sale, the sales channel and the total sales dollars for the product on that specific date.

Download the sample file

Our Goals

We will start from a source table that has the following columns:

  • Date
  • ProductName
  • ChannelName
  • Amount

Our goal is to create a final report that summarizes that data and lists:

  • The total amount per day for each channel,
  • The total products sold in that channel,
  • All the products sold, and
  • The product with the highest sales.

We can break these into three separate sub-goals:

  1. Create the column that sums all the amounts by date and channel,
  2. Create a new column that represents a list of all the products separated by a commas, and
  3. Create two extra columns that give us the name of the top selling product and its proportion of the total sales.

Let’s find out we can get from the Source Table to the Desired Result.

The Start

Once you open the workbook, head over to the Sales worksheet, where you’ll find a table. Select any cell inside that table, click the Power Query tab and choose From Table.

You’ll now be launched into the Power Query editor.

Goal 1: Grouping and Total Sum Column

Our first step starts by grouping the rows in our table using some criteria. Click the Group By button and set it up using the following criteria:

That should give us a table with a fewer amount of rows because all the data has been grouped by Date and Channel Name. We’ve even managed to create some new data as well, by creating a SUM of the Amount column, and a Count of Distinct Rows (yielding a count of distinct products by channel by day). We’ve also got a list of all products in the final step, which will make a bit more sense later…

A Best Practice: Define DataTypes

Whenever you pull a date into Power Query, we highly recommend that you specifically define the datatype for the date column. If you don’t, the data could be treated as type “any”, which means that it could land as either text or a value in your output instead of a date. It’s not hard to do at all, simply select the column header, go to the Transform tab, and change the Data Type to Date:

So we’ve now got a Total Sales and a Total Products by Channel, finishing our first goal.

Goal 2: Create a Comma Separated List of all Products Sold Each Day

Our next step is to create a formula for a new column that somehow:

  • Takes all the products that were sold on that specific date and channel
  • Creates a list of those products
  • Transforms that List into a table
  • Transposes that table into a table with multiple columns
  • Merges all those columns delimited by a comma

Formula List

This is the formula that does just that using Table.ToList Table.Transpose Table.FromList Table.Column and the Combiner function:

Table.ToList( Table.Transpose(
Table.FromList(
Table.Column([Products], "ProductName")
)
),
Combiner.CombineTextByDelimiter(", ")
)

What this formula is doing?

To better explain what this formula is doing, we are going to do each part of that formula as a new step. The formula defined above basically does all of these steps in a single step.

We start with the most inner function and that is Table.Column([Products], “ProductName”):

What this formula does is simply extract a column from a table and present it to us a List. So we end up having a list with the values from the ProductName column.

The next step is to transform that list into a Table so it can be easier for us to perform other type of operations. The function that transform a list into a table is called Table.FromList and we’ll use it now:
TableToListProductName

As you can see from the picture above, the new column has Table values for that whole column. We did that because we want to use another function to transpose those rows into columns. That function is Table.Transpose and that formula should read like this:

TransposeProductName

The result of that is basically a transposed table. So if we had N amount of rows and only 1 column now we’ll have 1 row with N amount of columns.

Our next step is to combine all the columns using a format similar to “Value1, Value2, Value3, …, ValueN”.

To do that, we use a Table.ToList that automatically does the operation of concatenating all of the strings into a single value. By Default, it uses a comma separator but in this case we want to go with a comma followed by a space (, ) and the second argument of Table.ToList allow us to do so by adding a Combiner function.

In our case, we’ll use this combiner function: Combiner.CombineTextByDelimiter(“, “) that does just exactly what we need.

And once you expand that column by clicking the arrows that go in opposite directions, and once we rename that column, this is the result of it:

RenamedColumn

We created the column that creates the list of all the products that were sold on that day, so we finished our 2nd goal. Let’s go to the next one.

Goal 3: Add columns for top sold Product and participation of it

Our next step is to extract the top product and also its amount on a row by row basis from the column Products which is this case is a table. We can do so by using a new custom column with the following Table.Max function:

Ranking

Once you create this new column, you’ll notice that it’ll populate with records which is a special type of data representation in Power Query and what we need is to extract the ProductName and Amount from that record.

We can do so by simply clicking on the opposite arrows icon that are next to the name of this new column. Click on that icon and you’ll notice a new selection window where you can select what columns you want to extract from that record. For now, just select the ProductName and the Amount columns.

Once we rename those columns this is how our table should look like:

Reportpreview

We are almost there! we are missing the division of the Top Product amount over the Total Sales so we can get a % out of it. Let’s do so by adding a new custom column:

Divide

After spending some more time cleaning the data, this should be the result of our hard work:

Final Result

You can click on Close & Load to save this table to your Workbook or inside your Data Model.

Note that you can refresh this at any time and it’ll work as desired.

Transform it into a Function

There are many ways to transform this into a function, but the basics of creating a function go into making a part of the whole code a variable.

Which one would you choose to make a variable? Let us know in the comments section below

This time, we’re choosing a simple variable. Since we need the Source step to be a table, let’s define that as our variable.

  1. First we go to the View tab and click on Advanced Editor
  2. Then we look at the M code in this Editor and replace the one that we’re trying to make a variable
  3. We replace it with a variable and add the variable before the code as shown in the picture below

Function

Now we need to put this to the test. Let’s create a blank query and grab all the tables from our current Workbook using Excel.CurrentWorkbook():

Excel.CurrentWorkbook

Next we add a custom column by using the name of the function that we just created which is the name of the Query SalesReport and the result should look like this:

ResultFunction