none
Data formatting issue (bug?)

    Question

  • I import data using OData. When I send results to a worksheet, the data is formatted properly. Specifically, numbers are treated as numbers. But if I send the data to a PowerPivot data model, everything is formatted as text which means things like filtering and sorting are broken. 
    Wednesday, November 13, 2013 8:58 PM

Answers

  • Thanks for the details.

    The cell-level formatting on the PQ Query Editor is based on cell value type inference, while the ribbon Data Type value is based on schema information. If this dropdown value is empty, it means that the column is of type Any, which basically means we've found some issue trying to understand the OData feed schema.

    Is there any chance that we can take a look at the query and the service metadata document so we can troubleshoot the issue?

    Thanks,
    M.

    Friday, November 15, 2013 4:18 PM
    Owner

All replies

  • Hi,

    Could you verify the column types within the Power Query editor when you import from OData? You will see the type in the Editor ribbon under the "Transform" group.

    Also, did you apply any transformations to the columns in PQ before loading your query?

    Thanks,
    M.

    Thursday, November 14, 2013 5:34 PM
    Owner
  • In the "Transform" group, the "Data Type" has no selection. I assume this means that there is an issue with the metadata. What is the best approach to diagnose this?
    Thursday, November 14, 2013 9:28 PM
  • In the query preview, the data looks correct. Numbers are right-aligned and when I sort and filter it is being treated as a number. But the "Data Type" value is empty in "Transform" as I had indicated in my previous post.  Before I spend too much more time troubleshooting, I'm hoping that could explain where/how Power Query decides what data types to assign to a column.  I see nothing wrong with the metadata, so I'm probably not looking at what really matters.
    Thursday, November 14, 2013 9:49 PM
  • Thanks for the details.

    The cell-level formatting on the PQ Query Editor is based on cell value type inference, while the ribbon Data Type value is based on schema information. If this dropdown value is empty, it means that the column is of type Any, which basically means we've found some issue trying to understand the OData feed schema.

    Is there any chance that we can take a look at the query and the service metadata document so we can troubleshoot the issue?

    Thanks,
    M.

    Friday, November 15, 2013 4:18 PM
    Owner
  • Hi Miguel,

    With the confirmation that it is the metadata at fault, I'll spend some more time trying to diagnose. If I cannot figure it out then I'll post back to this thread.

    Thanks,

    Chris

    Friday, November 15, 2013 4:23 PM