Forum Discussion

Tairo's avatar
Tairo
Helper I
8 years ago

Invalid OADate value error

I createded a dashboard using source data in a csv format. Now I connected the PBI dashboard to Table Storage in Azure. Basically this works fine but when I want to add a measure in a PBI table I get an error: Invalid OADate value error

 

Does anybody knows what is causing this and how I can fix it?

 

Feedback Type:
Frown (Error)

Timestamp:
2018-02-05T08:58:25.6156476Z

Local Time:
2018-02-05T09:58:25.6156476+01:00

Session ID:
290c7dcc-0902-4366-b5cb-133a624c9539

Release:
December 2017

Product Version:
2.53.4954.621 (PBIDesktop) (x64)

Error Message:
Invalid OADate value '16482401.6979167'. Accepted values are between -657435.0 and 2958465.99999999

OS Version:
Microsoft Windows NT 10.0.10586.0 (x64 en-US)

CLR Version:
4.6.1 or later [Release Number = 394254]

Peak Virtual Memory:
33.8 GB

Private Memory:
310 MB

Peak Working Set:
426 MB

IE Version:
11.589.10586.0

User ID:
5f91232f-6cac-413e-a13e-7d612afe106f

Workbook Package Info:
1* - en-US, Query Groups: 0, fastCombine: Disabled, runBackgroundAnalysis: True.

Telemetry Enabled:
True

Model Default Mode:
Import

Snapshot Trace Logs:
C:\Users\Arno\AppData\Local\Microsoft\Power BI Desktop\FrownSnapShot1654201909.zip

Performance Trace Logs:
C:\Users\Arno\AppData\Local\Microsoft\Power BI Desktop\PerformanceTraces.zip

Disabled Preview Features:
PBI_shapeMapVisualEnabled
PBI_EnableReportTheme
PBI_numericSlicerEnabled
PBI_SpanishLinguisticsEnabled
PBI_daxTemplatesEnabled
CustomConnectors
PBI_reportBookmarks
PBI_AdobeAnalytics
PBI_qnaExplore

Enabled DirectQuery Options:
PBI_DirectQuery_Unrestricted

Cloud:
GlobalCloud

Activity ID:
null

Time:
Mon Feb 05 2018 09:51:05 GMT+0100 (W. Europe Standard Time)

Error Code:
InvalidDateTimeValue

OData Error Message:
Failed to get the value of the column at index 6 from the data reader.

DPI Scale:
100%

Supported Services:
Power BI

Formulas:


section Section1;

shared nouns = let
    Source = AzureStorage.Tables("heinekensamodel"),
    nouns1 = Source{[Name="nouns"]}[Data],
    #"Expanded Content" = Table.ExpandRecordColumn(nouns1, "Content", {"count", "neg", "neu", "noun", "pos", "review_id", "sen", "sentence_id", "sv"}, {"Content.count", "Content.neg", "Content.neu", "Content.noun", "Content.pos", "Content.review_id", "Content.sen", "Content.sentence_id", "Content.sv"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Content",{{"Content.sv", type number}, {"Content.sentence_id", type text}, {"Content.sen", type text}, {"Content.review_id", type text}, {"Content.pos", Int64.Type}, {"Content.noun", type text}, {"Content.neu", Int64.Type}, {"Content.neg", Int64.Type}, {"Content.count", Int64.Type}, {"Timestamp", type datetimezone}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"RowKey", Order.Descending}})
in
    #"Sorted Rows";

shared sentences = let
    Source = AzureStorage.Tables("heinekensamodel"),
    sentences1 = Source{[Name="sentences"]}[Data],
    #"Expanded Content" = Table.ExpandRecordColumn(sentences1, "Content", {"review_id", "sen", "sentence", "sentence_id", "sv"}, {"Content.review_id", "Content.sen", "Content.sentence", "Content.sentence_id", "Content.sv"})
in
    #"Expanded Content";

shared reviews = let
    Source = AzureStorage.Tables("heinekensamodel"),
    reviews1 = Source{[Name="reviews"]}[Data],
    #"Expanded Content" = Table.ExpandRecordColumn(reviews1, "Content", {"actieve_fase", "categorie", "categorie_nivo_1", "categorie_nivo_2", "categorie_nivo_3", "categorie_nivo_4", "contact_nr", "count", "event", "gesloten", "in_behandeling", "ingevoerd_op", "jaar", "maand", "medium", "merk", "neg", "neu", "nieuw", "omschrijving", "pos", "prio_1", "prio_2", "prio_3", "prio_4", "prioriteit", "sen", "subcategorie", "submerk", "sv", "type_contact"}, {"Content.actieve_fase", "Content.categorie", "Content.categorie_nivo_1", "Content.categorie_nivo_2", "Content.categorie_nivo_3", "Content.categorie_nivo_4", "Content.contact_nr", "Content.count", "Content.event", "Content.gesloten", "Content.in_behandeling", "Content.ingevoerd_op", "Content.jaar", "Content.maand", "Content.medium", "Content.merk", "Content.neg", "Content.neu", "Content.nieuw", "Content.omschrijving", "Content.pos", "Content.prio_1", "Content.prio_2", "Content.prio_3", "Content.prio_4", "Content.prioriteit", "Content.sen", "Content.subcategorie", "Content.submerk", "Content.sv", "Content.type_contact"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Content",{{"Content.pos", Int64.Type}, {"Content.sv", type number}, {"Content.neu", Int64.Type}, {"Content.neg", Int64.Type}, {"Content.merk", type text}, {"Content.medium", type text}, {"Content.maand", Int64.Type}, {"Content.jaar", type text}, {"Content.actieve_fase", type text}, {"Content.categorie", type text}, {"Content.categorie_nivo_1", type text}, {"Content.categorie_nivo_2", type text}, {"Content.categorie_nivo_3", type text}, {"Content.categorie_nivo_4", type text}, {"Content.count", Int64.Type}, {"Content.event", type text}, {"Content.ingevoerd_op", type datetime}, {"Content.nieuw", Int64.Type}, {"Content.omschrijving", type text}, {"Content.prio_1", Int64.Type}, {"Content.prio_2", Int64.Type}, {"Content.prio_3", Int64.Type}, {"Content.prio_4", Int64.Type}, {"Content.prioriteit", Int64.Type}, {"Content.sen", type text}, {"Content.subcategorie", type text}, {"Content.submerk", type text}, {"Content.type_contact", type text}, {"Content.in_behandeling", Int64.Type}, {"Content.gesloten", Int64.Type}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each [Timestamp] <> null and [Timestamp] <> ""),
    #"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", each [Content.ingevoerd_op] <> null and [Content.ingevoerd_op] <> "")
in
    #"Filtered Rows1";

shared tokens = let
    Source = AzureStorage.Tables("heinekensamodel"),
    tokens1 = Source{[Name="tokens"]}[Data],
    #"Expanded Content" = Table.ExpandRecordColumn(tokens1, "Content", {"count", "pos", "review_id", "sen", "sentence_id", "sv", "token"}, {"Content.count", "Content.pos", "Content.review_id", "Content.sen", "Content.sentence_id", "Content.sv", "Content.token"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Content",{{"Content.count", Int64.Type}, {"Content.pos", type text}, {"Content.review_id", type text}, {"Content.sen", type text}, {"Content.sentence_id", type text}, {"Content.sv", type number}, {"Content.token", type text}})
in
    #"Changed Type";

2 Replies

    • Tairo's avatar
      Tairo
      Helper I

      v-xjiin-msft, thanks for your response.

       

      I figured it out. In Edit Query I set the date/time format manually. That created the issue when using the measure.

       

      I deleted the database and uploaded it again and instaed of manually set the date/time format I used "detect data type" to format the date field. That solved the issue.

       

      Regards,

       

      Tairo