Hi Michelle,

The problem with the text file is that it is delimited by a space but it has several consecutive spaces. In Excel we can tell it to 'treat consecutive delimiters as one', but in Power Query that option is not available in the GUI.

There is a function called Splitter.SplitTextByWhitespace you can use and you'll see it in the attached file.

You'll see I also transposed the data so the two header rows could be merged together into one, I then transposed it back before promoting the first row to headers. You never want your header row split over two.

Also, there wasn't any transaction data in the attached file, but you'll get the idea because there were some empty rows included.

You'll get an error when you open the query because it will be looking for your text file saved to my PC. You can change the file path and name by editing it in the Advanced Editor. You'll see it on the second line of code i.e.

let
Source = Table.FromColumns({Lines.FromBinary(File.Contents("C:UsersmyndaOneDrive - My Online Training HubTrainingTraining ContentMember's Questionsmichelle-01032012.txt"), null, null, 1252)}),
#"Removed Top Rows" = Table.Skip(Source,8),
...

Mynda

I had a few other files there with the one you sorted .

How do I  get the date from each file. Seen enclosed zip files Jan 06 2012 .

I thought that once one file has been sorted then all other files that are the same in a folder will get sorted the same way.

Each file is a daily file and I need the dates in the excel format once power query sort the .txt file.

you mention

There is a function called Splitter.SplitTextByWhitespace  . where is this in power query?

Thank you

Hi Michelle,

There was no zip file attached. However, if you get files from a folder then you make your transformations in the 'Sample' query as explained in session 2.05 of the course.

The functions in Power Query aren't all available via the GUI, and Splitter.SplitTextByWhitespace is one of them that you won't find in the GUI. Functions can be used in a Custom Column calculation, or you can edit the M code in the Advanced Editor. Here is a resource page for all Power Query functions. I often browse through this when looking for a solution that isn't already built into the GUI.

Mynda