• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Excel Raw Data to Excel Table

SollyEng

New Member
I am looking for a way to convert raw data in an excel report to a excel table. There is a lot of data.
 

Attachments

  • Consolidated Reports short.xlsx
    87.5 KB · Views: 11
What do you want your report to look like. We are not mind readers here. Suggest you mock up a result table so that we know and understand what you need.
 
Good Day. I am trying to turn the data into an excel table that can then be used as a pivot table for analysis and visualisation purposes.
 
You have not addressed my request by supplying a sample of what your expected output should look like.
 
Broadly, you need to use Power Query.
The merged cells need to go; transposing the data and fill-down in PQ would achieve that.
 

Attachments

  • Consolidated Reports short (PB).xlsx
    209.7 KB · Views: 7
Excellent Peter! What guidelines can you give me on how to do this? I would like to learn how to do this with Power Query. Is there some tutorial or reading that you can suggest please?
 
Hi @SollyEng
The bad news is that I am not the person to guide you since I am a Power Query novice. I suppose the good news is that, if I can transform your data as a novice, so can you!

Mynda Treacy has a fair amount of freely available training material in addition to her paid courses.
https://www.myonlinetraininghub.com/category/power-query

Mike Girvin has also posted a series of videos on power query including one on the M-language that underpins the GUI-driven functionality
 
@zosiano
In one of his videos Oz du Soleil (http://datascopic.net/) made the point that on occasion the correct action is not to work on dirty data; the proper action is to reject it and return it to the originator for correction. If you have to make 'best guesses' to give clean data then you may simply be locking errors into an apparently 'clean' dataset.
 
Back
Top