I wrote a blog post on the New Excel Data Types in May and stated that “the Sky is the limit” in the way the New Excel Data Types can affect the way we work with Excel… And one month later, here is a first Excel feature that is greatly enhanced by The New Excel Data Types. It’s the good old Auto Filter. If you don’t know what the new data types are please read the post I wrote back in May to get the basic understanding of what they are and how they work. This post will only focus on the way they enhance the Auto Filter. And you should keep in mind. Right now (June 2018) the New Data Types are only available in Office 365 Insiders Fast build.
Let’s start with a simple Excel table containing City names.
And if we open the Auto Filter drop-down at the top we get
Nothing special up to now I hear you say…
Oh but wait…
The New Excel Data Types Enhancing the Auto Filter Experience
Since these cells are the Geographical Data Type, the City Name is an entity and that cell contains much more data than just the Name of the City
And if you look at the Auto Filter Drop-down again, you will see that there is a new field on the top. It says (Display Value) and allows you to choose any field contained within the City entity. In our case, we’re going to choose Country/Region.
And now look at that. Even though the column only contains the City Names you can now filter the City column by Country… Keep in mind that the name of the Country is not the content of the cell.
And if we now choose the United Kingdom from the List of Countries and click Ok
In this post, we’re going to talk about importing CSV files with power query! This is a guest post written by John MacDougall, an Excel MVP from the US. John runs a famous Excel blog called How To Excel where you can find tons of useful tips and tricks. In this post, he will explore the use of Power Query for Importing CSV files into Excel. But not just plain CSV files. CSV files that have different number of rows of metadata.
You might be getting your data from another system like a general ledger, CRM, admin system or any other system that has data you need to report on.
A lot of the time, these will export data as CSV files.
If the CSV files you’re importing are nicely formatted with column headers in the first row of the file, then it’s an easy process to import into Excel with power query. We can select a From Text/CSV query from the Data tab, and pick the file from the resulting file picker menu. From there we can either load the data or further edit it in power query.
If you’ve never seen or heard of power query, then you can learn about power query here. It’s essentially a tool you can use to import data into Excel and then transform the data.
Problems Importing Files with Metadata
Sometimes, it might be the case that these files contain metadata. This is just data about the file such as when the report was run or the period it covers. Usually, these will be in the first couple of rows of your CSV files.
In the above example, it’s not until the 10th row that we see the columns headers and the report data because in rows 1 through 9 we have our metadata information.
This is easy enough to deal with if we always have 9 rows before our column headers start. We can remove the top 9 rows in our query then promote the first row to the column headers.
But what if this changes? Maybe some CSV files will have only 8 rows because there is no account contact information in the system.
If we try to run our query on a file with less than 9 rows of metadata, then we’ll end up promoting the wrong row as column headers and our query will throw out an error in the next step where we try and reference any column.
Can we avoid this and make it dynamic so that no matter what row our data starts on the query will remove the correct number of rows?
Import the CSV
The first step is to import a single CSV file. Go to the Data tab and select a From Text/CSV. In the resulting file menu picker, navigate to the folder where your reports are stored and select one of the CSV files.
You can select any of the reports regardless of which row the column headers appear in.
Press the Edit button to open the query editor.
Power query will also add a Change Type step to guess the data types of the different columns. We only want the imported data as a source without any other transformation steps applied to it , so we can delete this step with a left click on the X next to the step under the Applied Steps area.
Now we can Close & Load the query as a connection only. I’ve named the query ImportData.
Get the Row Number of the Column Headings
Now we will create a query to get the row number which contains the column headings. Remember, this was something that might change depending on the file.
In the Queries & Connections window pane, right click on the ImportData query and select Reference from the menu. This will create a new query with the ImportData query as the source.
Now we should be in the query editor with our raw data that includes the metadata rows. Go to the Add Column tab and press the Index Column button. This will add an index row starting at 0 in the first row and incrementing by 1 each row.
This index column will tell us the row number that our column headers are in.
We can now filter the first column of the data. Left click on the filter icon in the column heading and then deselect everything except for Product. Our column headers are always named the same across our different files, so this will always result in the row containing the column headers being left.
Now our column headers will be in the first row. We can then right click on the first row value in the Index column and select Drill Down from the menu.
This will turn our query results into a single value which corresponds to the number of rows of metadata we need to remove from our file. We can name the query GetFirstRow and then Close & Load it as a connection only.
Remove the Metadata
We have obtained the row number which contains the column headings in the data using an index column, a filter and then drilling down on a single value. Now we can use this value to remove rows from our file.
We can reference the ImportData query again with a right click and selecting Reference from the menu.
Now we are ready to remove our metadata rows. From the Home tab press the Remove Rows button and select Remove Top Rows from the menu. When the pop up appears that asks how many rows to remove, we can enter any value as a temporary placeholder. Let’s enter 9 for now.
Now we need to edit the M code for the last step in the formula bar. We need to replace the placeholder 9 value that we entered with GetFirstRow. This was the name of our query that gave us the single value of the row number containing our column headers. Press Enter to confirm the edit to the formula.
Now we can promote the first row to column headers. Go to the Home tab and press the Use First Row as Headers button.
Change any of the data types as needed, then name the query Report and Close & Load it to a table in the Excel.
Within our changing data format, we were able to find and use the column heading names which remained constant to dynamically get the number of rows we needed to remove.
Whenever you’re faced with changing data formats look for the constants (I like to call these data anchors) and use them to get the results you’re after.
Now if we change the path and file name in the ImportData query to point to another report and it has a different number of metadata rows, our query will still work! We could even parameterize our query with a path and file name parameter so we can easily import any file from any folder, and we don’t have to worry about the changing rows of metadata.
New Data Types in Excel will undoubtedly be THE talking point of the near future Excel evolution. That being said, they are also the reality in Office 365 Fast Insiders. That’s where they were recently introduced by Microsoft. This post will first explain what the new Data Types in Excel are and then we will show some examples of the Data Types in Excel in Action. But we will finish off with what would probably be the fourth verse of Imagine by John Lennon. I will explain what the new Data Types in Excel mean for you and how I see them developing in the future. After you’ll read “the fourth verse”, you should realize why the new Data Types in Excel are a true game changer and why they DO MATTER for you!
What are the New Data Types in Excel
The New data Types are technically Linked Data Types. This means they pull data from an Online Source. Right now, there are two New Data Types in Excel, Stocks and Geography.
But for Understanding, you can think of the New Data Types in Excel as Entities. Every Cell contains a “hidden lookup row” that turns the text within a Cell into a “record” with properties.
A cell containing the name of an Entity (like the name of the City)
Ends up containing this…
But now, you can call any property you want in two ways…
like a formula
or just adding Columns to a Table either Manually or by clicking on the Icon in the top right corner of the Table (see the above image)
If you think about it, it’s as if a cell contains much more information than just it’s content. From today’s Excel standpoint, you can really think about it as if there is a lookup Table. Since these are Linked Data Types the data is actually online but you are still just calling the data you need. And since the Cities are in an Excel Table, you can just throw a new City in there… Let’s add Sofia (Bulgaria)
And there it is. This is the future people. Right now, you can find (if you are running the latest version of Excel) the New Data Types on the Data tab under Data Types.
There are only two new Data Types available at the moment but Microsoft plans to add new Data Types. You can read more about that here and here. Also at the time of this writing the only language that is supported is English.
Short Demo of the New Data Types
Before discussing the future of The New Data Types in Excel as I see it, here is a short gif of the whole process as it works today
Future of the New Data Types in Excel
The following is not an official Microsoft standpoint, it’s just my personal view of the possibilities of the new Data Types in Excel.
The way I see the New Data Types progress (in addition to of course being many more of them) is that each company will be able to utilize them in their own way. Imagine being an HR Specialist in a Company. At this point all the data about your employees is stored in a Database, Excel or even in a SharePoint List. Now imagine entering the name of one of your employees in a Cell in Excel. Then you just go Data/Data Types/Human Resources. It recognizes the name and gives you the ability to use all the data about that employee that the company has without writing a single lookup.
The same could be true for the Company Products, Customers… The Sky is the limit! Thinking about it this way, you can surely see the incredible value this brings to Excel users.