Often we get data which is distributed in multiple column sets. Here is how you can convert it to good data (tabular column format) using Power Query. Watch the video. Sample File download.
This is the worst type of input data to get. Each row has been split into two (or sometimes more) rows. We want those to be combined into a single row. In an earlier article, I have shown one method of doing this.
Now here is a simpler, faster and more powerful method using Power Query.
(Estimated reading time 12 min)
Thank you for the overwhelming response for Data Analytics webinar video. More than 800 attendees, 70+ questions.
Watch the video on YouTube (use 720P) or Download (MP4, 188 mb, 60 min) and view on your PC. A big thank you to the Microsoft and Economic Times team for making this possible.
Power Query is an amazing tool for importing and cleaning up data from various sources. The problem was that it required you to have professional version of Office. Now that problem is solved…
There are two simple approaches in reversing row order. One is to add an extra column with serial numbers and then sort on that column in descending order. This works for small amount of data. For large data it is best to import it in Power Query and choose Transform tab – Reverse Rows.
Benefit of Power Query? It works on a smaller sample of data and then applies the transformation when you choose Save and Load option. This is much faster than getting all the data and then trying to sort it (which is the first method).
Why is this required? Usually required with logs where the first transactions or rows are at the bottom. So the data is received in reverse chronological order. Twitter feeds, Timeline Updates, Live blogs – all follow this pattern.
This method works independent of the time-stamp column. What is wrong with timestamp? It may be in different time formats, some rows may have same timestamp and some rows may have no timestamp at all.
What a brilliant feature, which was lying right in front of me for months, but I did not notice it. In Power Query Preview dialog, every field name has a hyperlink. NOTICE THAT. Click on a column name to bring that column into view.
Now you never need to struggle with horizontal scrolling in complex data sets.
This is a GIF animation. Some colors are smudged due to GIF limitations.
Have you noticed the hyperlinks which appear in formula syntax tooltip in Excel?
Find out what that does and you will be amazed. Read this article for details.
This is a series of articles about people like me who deliver demos, conduct workshops and speak at keynotes.
I will share what I have learnt about delivering high-impact demos
Estimated reading time 7 min.
In the previous article we saw the problem with importing CSV files.
Here is how the new Power Query can be used to clean the data while importing.
Estimated reading time 10 min
Recently I conducted a session for BI team of a large customer.
This introduces the concept of end user BI to hard core developers.
Have a look. It will give you a different perspective.
2 hour video.
Reports generated from business applications often become inputs to new reports.
You end up spending too much time cleaning them up. Here is a new and unimaginably faster way of doing it – using Power Query.
Estimated reading time 15 min
Estimated time saving huge!