Page 2 of 2 FirstFirst 1 2
Results 11 to 19 of 19

Thread: PQ field defaulting to text

  1. #11


    Register for a FREE account, and/
    or Log in to avoid these ads!

    Quote Originally Posted by millhouse123 View Post
    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"

    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.

  2. #12
    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.

    Click image for larger version. 

Name:	Default text2.png 
Views:	9 
Size:	38.9 KB 
ID:	7436

  3. #13
    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.

  4. #14
    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.

  5. #15
    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.

    20170801 MDL (MNL) FIA Performance Attribution and Risk Report.xls20170801 MDL (MNL) FIA Performance Attribution and Risk Report.xlsxTest File.xlsxTest File xlsx.xlsx

  6. #16
    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.

  7. #17
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,360
    Articles
    46
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    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, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  8. #18
    Quote Originally Posted by Ken Puls View Post
    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,

    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.

  9. #19
    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/...tabase-errors/

Page 2 of 2 FirstFirst 1 2

Posting Permissions

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