+ Reply to Thread
Results 1 to 7 of 7

Power Query Text File

  1. #1
    Forum Contributor
    Join Date
    05-24-2012
    Location
    Cork, Ireland
    MS-Off Ver
    Excel 365
    Posts
    144

    Power Query Text File

    Hi All,

    I have a text file I'm trying to modify via Power Query.

    If I attempt to open the file directly in excel, the Text to Columns wizard appears and correctly identifies the general column structure in the file. However, when I try to import the same file through power query, it lumps everything in a single column making it very difficult to manipulate.

    I've attached a sample of the data - any help would be great!

    Thanks.
    Attached Files Attached Files

  2. #2
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,775

    Re: Power Query Text File

    The reason why Power Query is having issue importing data, is that your text file does not have consistent column structure.

    You have areas where column conforms to "--- ---" type structure, and where it does not.

    If you need to use Power Query to import the data, you must first identify rows where data does not follow consistent structure.

    Ex: Row 6, 7, 9, 10, 23, 24 etc (basically any rows where Text.Length() of columns is less than 129). Row 1 is exception as it should be merged with row below.

    Then filter those rows out and then use fixed positions to split the data.

    Do you have sample of result that you are after?
    ?Progress isn't made by early risers. It's made by lazy men trying to find easier ways to do something.?
    ― Robert A. Heinlein

  3. #3
    Forum Contributor
    Join Date
    05-24-2012
    Location
    Cork, Ireland
    MS-Off Ver
    Excel 365
    Posts
    144

    Re: Power Query Text File

    Thanks for your reply.

    Yes, I can understand the point about the inconsistent column structure. I just find it strange that the Text to Columns wizard Fixed Width default does a much better job at figuring the column structure than Power Query.
    I thought maybe I was missing something in Power Query.

    I've attached what I'm looking for in terms of output.
    Attached Files Attached Files

  4. #4
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,775

    Re: Power Query Text File

    Try pasting in below code in "Advanced Editor" of Power Query. Change Source to your actual file path.

    Please Login or Register  to view this content.

  5. #5
    Forum Guru Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,284

    Re: Power Query Text File

    Or this:

    Please Login or Register  to view this content.
    let Source = #table({"Question","Thread", "User"},{{"Answered","Mark Solved", "Add Reputation"}}) in Source

    If I give you Power Query (Get & Transform Data) code, and you don't know what to do with it, then CLICK HERE

    Walking the tightrope between genius and eejit...

  6. #6
    Forum Contributor
    Join Date
    05-24-2012
    Location
    Cork, Ireland
    MS-Off Ver
    Excel 365
    Posts
    144

    Re: Power Query Text File

    Thanks so much - very clever!

    CK76 - can follow this fine, have replicated in my dataset.

    Olly - this works great also but I'm a bit lost as to how it does it! In the Source step - are you using the Fixed Width option as the delimiter to determine column widths? Then some magic with Transpose to set the correct headings and somehow all the data comes back in the 'Rename Columns' step!

    Thanks to you both.

  7. #7
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,775

    Re: Power Query Text File

    You are welcome and thanks for the rep.

    Olly's method does indeed use Fixed Width option to set which position to set new column (split operation uses same logic).

    His method is more elegant than mine, but may not be as easy to follow. Mine was done using GUI for the most part with some custom columns to make it easier to follow.
    As well, since I use US format for dates, I used text manipulation, rather than using implicit conversion to date type column.

    I'd recommend studying his method to learn PQ more in depth. It will give you more tools to use in data transformation.

    If your issue is resolved, please mark the thread as solved. Using Thread tools found at top of your initial post.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Prompt for file when getting data for Power Query
    By Joe.schuch in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-07-2019, 11:37 AM
  2. [SOLVED] Power Query results to a .csv file
    By JohnnyBoyxxx in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-16-2019, 02:39 AM
  3. [SOLVED] Power Query: Reading a text or csv file that is publicly available on the Web?
    By learning new things in forum Excel General
    Replies: 6
    Last Post: 10-31-2018, 03:12 PM
  4. Sub-Forum for Excel Power Tools (Power Query, Power Pivot & Power BI)
    By chullan88 in forum Suggestions for Improvement
    Replies: 10
    Last Post: 06-28-2018, 02:25 PM
  5. [SOLVED] Excel Power Query with file being used
    By taylorsm in forum Excel General
    Replies: 34
    Last Post: 11-02-2017, 05:57 PM
  6. Replies: 11
    Last Post: 08-01-2017, 06:08 AM
  7. Power Query how do i reuse on a new file
    By thorrrr in forum Excel General
    Replies: 3
    Last Post: 05-03-2014, 02:07 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1