From the course: Excel: Managing and Analyzing Data

Unlock this course with a free trial

Join today to access over 24,400 courses taught by industry experts.

Solution: Data preparation in Excel

Solution: Data preparation in Excel - Microsoft Excel Tutorial

From the course: Excel: Managing and Analyzing Data

Start my 1-month free trial Buy for my team

Solution: Data preparation in Excel

“

(upbeat music) - [Instructor] And here's one solution to the data prep challenge. There are so many ways you could have done this. The result is most important. Here we go. Cursor is in the dataset, I am in the data tab. I'm going to go up to Get Data From Table/Range, and the table does have headers, okay. Now I'm going to, with this column still highlighted, right-click, Fill, Down. And then rename this as Month. City/Day has to be separated out. Highlight that column. Split column by delimiter. Split it by custom. And the custom is going to be space, hyphen, space. And okay. Call this City and then Day. Now this bit is going to be tricky. I'm going to hold down the Shift key, go out to Day, right-click, Unpivot other Columns. Next I'm going to call this, instead of Attribute, Items. And while I'm here, I'm going to turn this into currency. There we go. And what we wanted to do is sort by the city. We can do that here in Power Query, or we can do it out on the grid in Excel. I'm…

Contents