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.
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
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.
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.
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...
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.
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.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks