M is for (Data) Monkey: stuck on first example!

[Tim Reynolds]

Hi all

I recently got the above book, by Ken Puls and Miguel Escobar, and have a problem with the first example file "Ch01-Delimited.csv", which I downloaded in the way described by the book (same problem occurs if I try the text file "Ch01-Delimited.txt" instead of the csv).

I am new to Power Query but managed to extract this file by following the instructions in the book. The problem is that the last column of data, called "Sum of Amount" is supposed to be Transformed automatically by PQ to numeric values, but it just stays as text.

The data in this column are all basic dollar/cent values, with negatives indicated by parentheses, e.g.

$2.73
($22.07)

If I right-click the column and try to change data type to Decimal Number or Currency, all the values in the column are replaced by the word "Error". Clicking on one of these errors shows the following message in yellow at the bottom of the screen.

DataFormat.Error: We couldn't convert to Number.
Details:
($22.07)

The other 3 columns are transformed as expected: the first contains dates and the next two contain numbers.

Can anyone explain why PQ is not transforming the last column as the book says it should and converting the text to numbers?

For info, I am running Office 365 v 1711 (Build 8730.2127) on Windows 10. I am in the UK, so default currency is £, rather than $, but surely PQ would not be stumped by that?

Any help would be appreciated. The book looks really good and useful; it's a shame to have got off to a rocky start.

Thank you, Tim

 

[oldbrewer]

maybe try the power BI area which mentions power query ?

 

[Norie]

What problems did you have downloading the file?

 

[Tim Reynolds]

Hi, thanks for the replies.

I downloaded the Ch01-Delimited.csv file ok, from the website given in the book: https://www.powerquery.training/book-files/. The file came in a zipped folder with all the other example files for the book, and I unzipped the folder fine. The csv file in question, when viewed in Notepad, starts like this:

TranDate,Account,Dept,Sum of Amount
12/1/2009,61510,150,($22.07)
12/1/2009,61520,150,($151.82)
12/1/2009,61530,150,($12.40)
12/1/2009,61540,150,($0.92)

... and continues similiarly for 2000+ lines, the final line being:

Grand Total,,,"$169,389.86"

Anyway, I imported the file to PQ from Excel as per the book's instructions: Data tab > Get Data > From File > From Text/CSV; in the Import Data box, I found and selected the file, in the location I'd unzipped it to, and clicked the Import button. This loaded a preview screen, on which I clicked the Edit button. The Query Editor window appeared showing all the data from the file. My only problem is that the fourth column has stayed as text and, as above, if I try to convert to numeric values, the column is filled with errors.

If you have any suggestions, I would be grateful. If you need any other information, please say and I should get back in a day or so.

I appreciate you time. Have a Happy New Year.
Tim

 

[Norie]

Tim

Don't have the book but I downloaded the file and loaded it into PQ.

I had the similar problems converting the 4th column until I went to Change Type and selected Using Locale....

I then selected Currency as the Data Type and English (United States) for Locale.

That seemed to work, well I didn't get any errors anyway and the column was converted.

 

[Tim Reynolds]

Hi again,

I have figured it out now. Select the fourth column, right-click and Change Type > Using Locale...

Set Data Type to Currency and Locale to "English (United States)".

This converted the text to values, as I had wanted.

Thanks again for your time.
Tim

 

[Tim Reynolds]

Cheers Norie - it is good to have this sorted.
HNY
Tim