1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. 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...

  3. 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

Discussion in 'Ask an Excel Question' started by SollyEng, Aug 21, 2018.

  1. SollyEng

    SollyEng New Member

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

    Attached Files:

  2. AlanSidman

    AlanSidman Active Member

    Messages:
    413
    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.
  3. SollyEng

    SollyEng New Member

    Messages:
    12
    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.
  4. AlanSidman

    AlanSidman Active Member

    Messages:
    413
    You have not addressed my request by supplying a sample of what your expected output should look like.
  5. SollyEng

    SollyEng New Member

    Messages:
    12
    Please see attached

    Attached Files:

  6. Peter Bartholomew

    Peter Bartholomew Well-Known Member

    Messages:
    498
    Broadly, you need to use Power Query.
    The merged cells need to go; transposing the data and fill-down in PQ would achieve that.

    Attached Files:

    Thomas Kuriakose likes this.
  7. SollyEng

    SollyEng New Member

    Messages:
    12
    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?
  8. Peter Bartholomew

    Peter Bartholomew Well-Known Member

    Messages:
    498
    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
    Thomas Kuriakose and SollyEng like this.
  9. SollyEng

    SollyEng New Member

    Messages:
    12
    Thanks for your assistance! Much appreciated!
  10. Peter Bartholomew

    Peter Bartholomew Well-Known Member

    Messages:
    498
  11. SollyEng

    SollyEng New Member

    Messages:
    12
    Thanking you kindly!
  12. Peter Bartholomew

    Peter Bartholomew Well-Known Member

    Messages:
    498
    @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.
    Marc L likes this.

Share This Page