PQ field defaulting to text

I am importing an Excel(.xls) file into power query and it is defaulting to a text data type. The problem is that the data I need from that field is numeric and it rounds the decimals up to the whole number. Is there anyway short of modifying the source so that it shows the decimals and does not round the numbers. Changing the source is not a good option since I will eventually be using the query as a function to bring in 100 xls files per month. Everything else with my PQ works great I just need the decimal values and would be very grateful for any help.

I tried changing the data type to decimal but that doesn't bring back the decimal values just the whole number. Most values in this column are numeric so not sure why Excel defaults to text.

Thanks

Without some sample data (and additional information about your culture codes) it is hard to tell, but may be you need to adjust your column type using an appropriate culture code, like in the pictures below.
That would be applicable e.g. if your textual numbers contain decimal points while your current culture code use decimal commas.

Attached is sample data before and after import. Note imported values are rounded with no decimals.

View attachment Sample data.xlsx

There are no queries in the file, you don't provide required information: you don't appreciate any help?

MarcelBeug said:

Without some sample data (and additional information about your culture codes) it is hard to tell, but may be you need to adjust your column type using an appropriate culture code, like in the pictures below.
That would be applicable e.g. if your textual numbers contain decimal points while your current culture code use decimal commas.

View attachment 7429View attachment 7430

Click to expand...

let
Source = Excel.Workbook(File.Contents("C:\Users\njm3546\Documents\OLAttachments\20170801 MDL (MNL) FIA Performance Attribution and Risk Report.xls"), null, true),
DailyReport1 = Source{[Name="DailyReport"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(DailyReport1,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type datetime}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}, {"Column10", type text}, {"Column11", type text}, {"Column12", type text}, {"Column13", type text}, {"Column14", type text}, {"Column15", type text}, {"Column16", type text}, {"Column17", type text}, {"Column18", type text}, {"Column19", type text}, {"Column20", type text}, {"Column21", type text}, {"Column22", type text}, {"Column23", type text}, {"Column24", type text}, {"Column25", type text}, {"Column26", type text}, {"Column27", type text}}),
#"Removed Top Rows" = Table.Skip(#"Changed Type",1),
Custom1 = Record.Field(#"Removed Top Rows"{0},"Column2"),
Custom2 = Record.Field(#"Removed Top Rows"{5},"Column5"),
Custom3 = #"Removed Top Rows",
#"Removed Columns" = Table.RemoveColumns(Custom3,{"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11", "Column12", "Column13", "Column14", "Column15", "Column16", "Column17", "Column19", "Column21", "Column22", "Column23", "Column24", "Column25", "Column26", "Column27"}),
#"Filtered Rows" = Table.SelectRows(#"Removed Columns", each ([Column20] <> "" and [Column20] <> "End") and ([Column18] = null or [Column18] = "BAML Futures" or [Column18] = "BAML PB" or [Column18] = "New inforce cash receivable" or [Column18] = "Option accrued" or [Column18] = "SocGen Collateral")),
#"Added Custom" = Table.AddColumn(#"Filtered Rows", "Custom", each if [Column18] = null and [Column20] = null then "DELETE" else "KEEP"),
#"Filtered Rows1" = Table.SelectRows(#"Added Custom", each ([Custom] = "KEEP")),
#"Removed Columns1" = Table.RemoveColumns(#"Filtered Rows1",{"Custom"}),
#"Added Custom1" = Table.AddColumn(#"Removed Columns1", "Custom", each Custom1),
#"Added Custom2" = Table.AddColumn(#"Added Custom1", "Custom.1", each Custom2),
#"Changed Type1" = Table.TransformColumnTypes(#"Added Custom2",{{"Custom.1", type date}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"Custom.1", "Date"}}),
#"Changed Type2" = Table.TransformColumnTypes(#"Renamed Columns",{{"Column20", type number}}),
#"Changed Type with Locale" = Table.TransformColumnTypes(#"Changed Type2", {{"Column20", type number}}, "en-US")
in
#"Changed Type with Locale"

Column 20 is the problem.. Even if I remove the change type step it still defaults to text instead of decimal.

P.S. I apologize for not including query in prior response. I missed that part of the request. I most certainly do appreciate your assistance and didn't mean to imply anything different.

Here is a full sample file


View attachment Full Sample File.xls

It is not very strange that Column20 defaults to text as it contains text:



At the end of your code, you change the type twice. Probably the first time results in whole numbers and the second time won't turn those numbers into decimals.
So remove the first of those 2 steps (#"Changed Type2"); then you should get numbers with decimals.

MarcelBeug said:

It is not very strange that Column20 defaults to text as it contains text:

View attachment 7435

At the end of your code, you change the type twice. Probably the first time results in whole numbers and the second time won't turn those numbers into decimals.
So remove the first of those 2 steps (#"Changed Type2"); then you should get numbers with decimals.

Click to expand...

Thanks for the advice, unfortunitally it does not fix the issue. To make this easier I cleared all my steps except the import and a single change type to Decimal. The data still shows up with no numbers to the right of the decimal.

Of course, you didn't use the culture code I already mentioned several times.

MarcelBeug said:

Of course, you didn't use the culture code I already mentioned several times.

Code:

let
    Source = Excel.Workbook(File.Contents("C:\Users\njm3546\Documents\OLAttachments\20170801 MDL (MNL) FIA Performance Attribution and Risk Report.xls"), null, true),
    DailyReport1 = Source{[Name="DailyReport"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(DailyReport1,{{"Column20", type number}}, "en-US")
in
    #"Changed Type"

Click to expand...

Sorry, this doesn't work either.. Any other thoughts?

millhouse123 said:

Sorry, this doesn't work either.. Any other thoughts?

Code:

let
    Source = Excel.Workbook(File.Contents("C:\Users\njm3546\Documents\OLAttachments\20170801 MDL (MNL) FIA Performance Attribution and Risk Report.xls"), null, true),
    DailyReport1 = Source{[Name="DailyReport"]}[Data],
    #"Changed Type with Locale" = Table.TransformColumnTypes(DailyReport1, {{"Column20", type number}}, "en-US")
in
    #"Changed Type with Locale"

Click to expand...

Do you get it to work when you use the full file I shared above along with the code you have suggested? It doesn't work for me, I would be interested to know if it works for you.

Just a remark: "it doesn't work" is generally not a good idea to formulate an issue. It is better to share either the error message, or - if there is no error message - explain how and why the actual results differ from the expected results.

Anyhow, yes your code works me, even without "en-US" because I have my culture code set to "en-US".
Of cours I get Errors because "End" can not be converted to a number, so if that is your definition of "it doesn't work", then it doesn't work with me either, but otherwise I get my decimal numbers.

I just realized the sample file I had provided was .xlsx where the source file I am using is .xls. Not sure why that would matter but I tested it and the full sample file works for me as well. Any thoughts on why it would behave differently and how to fix it? As described earlier in the thread my problem is that column 20 is being formatted as text and not a decimal.

You provided a xls file in post #6, which I used for my reactions.

It is not clear to me hat is different for you if you use the .xls or the .xlsx file.
You suggest that - if you start from scratch - with the xlsx file the type for column 20 defaults to decimal and with the .xls file it defaults to text.
If so, then you must have different data in column 20.

Remark: an Excel file with Power Query objects always has .xlsx; source data can be in a .xls file.

I started over and tested both .xls and xlsx and only the xlsx shows the numbers to the right of the decimal. the only difference in the source files is the file type. I have attached each of my sources files as well as the results. I would really like to figure out a solution as converting 100 files per month pretty uses up any time power query would have saved me. I appreciate your assistance.

View attachment 20170801 MDL (MNL) FIA Performance Attribution and Risk Report.xlsView attachment 20170801 MDL (MNL) FIA Performance Attribution and Risk Report.xlsxView attachment Test File.xlsxView attachment Test File xlsx.xlsx

Note that the values i am looking as start in row 86 column 20 for the .xls file after the power query has run and in row 89 in the .xlsx of the imported file.

One question... when you are formatting your data in power query as decimals, are you expecting to see that all the decimals line up? is the issue that one line shows as 20 and another shows as 20.25?

If that's the case, I just want to call out that Power Query does not apply number formatting, it applies data types. Excel does number formatting. When you land the data to an Excel table, are you still missing the decimals?

Ken Puls said:

One question... when you are formatting your data in power query as decimals, are you expecting to see that all the decimals line up? is the issue that one line shows as 20 and another shows as 20.25?

If that's the case, I just want to call out that Power Query does not apply number formatting, it applies data types. Excel does number formatting. When you land the data to an Excel table, are you still missing the decimals?

Click to expand...

Ken,

Thank you for your help, I am aware that PQ does not format the data. The problem is that with the .xls file PQ truncates the numbers to the right of the decimal. for example one of the numbers I have been looking at in the source file is 2.95, when I import using xls file it rounds it to 3, no matter how I format it all the numbers to the right of decimal are gone. The value is literally 3.. On the other hand .xlsx file import properly as 2.95. All of my source files are .xls which seem to be problematic in this case. Interestingly I wrote a macro to go through each file and show 5 decimals then the data imported with the decimal values. PQ is seeing these values as a string and not numeric when being imported.

Any help is much appropriated.

As you already noticed, xls files are imported as text, while .xlsx files are imported with type any. In both cases: before any types are changed.
It looks to me like you should either adjust the .xls files so the displayed values have the format required in Power Query, or convert the xls files to xlsx.

Anyhow, the issue seems to become very technical and out of my area of expertise.

I found the following link that might be helpful: https://powerbi.microsoft.com/en-us/documentation/powerbi-desktop-access-database-errors/