Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Customer experience | Sales, Customer Insights,... / Date format problem wh...
Customer experience | Sales, Customer Insights,...
Suggested answer

Date format problem when importing data into CI-D using PQ connector to Dataverse

(1) ShareShare
ReportReport
Posted on by CU03091316-0 33
I am getting error StartdatoMedlemsskap: "The datetime format expected is M/d/yyyy h:m:s a or valid ISO datetime format but found an invalid value or format" in Customer Insights - Data when importing from dataverse using powerquery connector.
 
The field in question is a datetime. We are using Norwegian locale and language in CI-D and in the powerquery editor in the connector, I have set Norway as the locale. In spite of this, it seems CI-D is expecting a US date format. I have also tried to force US locale by transforming on the indiviual field, but in spite of this the field appears as dd.mm.yyyy ..... in the spreadsheet in the PowerQuery Editor and it still fails when I load.
 
Any suggestions or links to a thorough article on handling dates from Dataverse to Customer Insights - Data via Power Query Connector?
Categories:
Dynamics 365 Customer Insights - Data
  • CU03091316-0 Profile Picture
    CU03091316-0 33 on at
    Date format problem when importing data into CI-D using PQ connector to Dataverse
    Could the issue be that Power Query has 3 locales for Norwegian and CI-D only one?
     
     
  • CU03091316-0 Profile Picture
    CU03091316-0 33 on at
    Date format problem when importing data into CI-D using PQ connector to Dataverse
    I tried solution 1. The problem with this is that the data type CI-D imports is then text. The column (and I have many more than one with this problem) needs to be date or datetime to be able to use the columns correctly in segmentation (all xxx after yyyy date....) or using tables as activities, where the timestamp is needed for calculating measures and putting them on the timeline....

    And I am stuck with having locale Norwegian in Power Query Editor. If not all choice fields show up in english instead of Norwegian.
  • CU03091316-0 Profile Picture
    CU03091316-0 33 on at
    Date format problem when importing data into CI-D using PQ connector to Dataverse
    If I go with option 1, will CI-D interpret it as text or date? I need it to be date to be used for activities to the timeline.
  • Suggested answer
    Saif Ali Sabri Profile Picture
    Saif Ali Sabri 381 on at
    Date format problem when importing data into CI-D using PQ connector to Dataverse
    My response was crafted with AI assistance, tailored to provide detailed and actionable guidance for your Microsoft Dynamics query.

    The issue you're encountering with date format during data import from Dataverse into Customer Insights - Data (CI-D) via the Power Query connector is a common challenge when dealing with localized date formats. Here's an explanation of why this happens and a set of recommendations to resolve it.

    Root Cause

    1. CI-D's Expected Date Format:

      • CI-D expects datetime values to either:
        • Follow the US format (M/d/yyyy h:m:s a) or,
        • Be in a valid ISO 8601 format (e.g., yyyy-MM-ddTHH:mm:ssZ for UTC).
      • The Power Query Editor might display the date in your local format (e.g., Norwegian dd.MM.yyyy), but CI-D struggles to interpret it correctly when loading the data.

    2. Locale Mismatch:

      • Setting the Norwegian locale in the Power Query Editor adjusts the display format but doesn’t necessarily change the underlying datetime representation in the dataset to a format CI-D can process.

    3. Dataverse DateTime Format:

      • By default, Dataverse stores datetime fields in UTC and represents them in ISO 8601 format (yyyy-MM-ddTHH:mm:ssZ), but the Power Query connector may interpret these values as localized based on the regional settings.

    Solutions

    1. Force the Datetime into ISO 8601 Format in Power Query

    The safest approach is to transform the datetime column in Power Query to match CI-D’s expected ISO 8601 format. Here’s how to do it:

    1. Open the Power Query Editor.
    2. Select the problematic datetime column (StartdatoMedlemsskap).
    3. Add a custom column with the following formula to explicitly transform it into ISO 8601 format: 
      powerquery 
      = Text.From(DateTime.ToText([StartdatoMedlemsskap], "yyyy-MM-ddTHH:mm:ss"))
    4. Replace or rename the new column as the original datetime column.
    5. Ensure the column data type is text (not datetime).

    This transformation ensures that CI-D will interpret the datetime correctly.

    2. Adjust the Locale of the Column

    If you want to continue using the localized Norwegian format, ensure the data is interpreted properly in Power Query:

    1. In the Power Query Editor, click on the datetime column.
    2. Go to Transform > Data Type > Date/Time.
    3. Click Column Locale (in the dropdown next to the data type).
    4. Set the locale explicitly to Norwegian (Norway).
    5. Load the data again and check for issues.

    While this may work, CI-D can still encounter issues if it doesn’t understand the localized format. Using ISO 8601 (Solution 1) is generally more reliable.

    3. Pre-Transform the Date Format in Dataverse

    If you have access to Dataverse, consider pre-transforming the date format at the source. For example:

    • Use a calculated column or a plugin to format the datetime field in ISO 8601 format before exposing it to Power Query.
    • Alternatively, you can create a new text field in Dataverse to store the preformatted ISO datetime string.

    4. Check CI-D Settings

    • Go to Settings > Locales in Customer Insights - Data and ensure the locale is set to Norwegian (Norway).
    • While this won’t solve the issue with input data directly, it helps CI-D interpret data consistently.

    5. Use Power Query M Code Debugging

    Sometimes the issue lies in how the Power Query connector interprets and applies transformations. To debug:

    1. Open the Advanced Editor in Power Query.
    2. Look for the M code that applies to the problematic column.
    3. Explicitly set the locale or transformation in the M code: 
      powerquery 
      #"Changed Type" = Table.TransformColumnTypes(#"Previous Step", {{"StartdatoMedlemsskap", type datetime}}, "nb-NO")
    4. Alternatively, replace the column transformation with an ISO 8601 format as in Solution 1.

    Suggested Reading

    For more information on handling dates in Power Query and Dataverse, you can refer to:

    1. Microsoft Docs: DateTime data type in Dataverse
    2. Microsoft Docs: Using Power Query to transform data
    3. Working with Power Query M code

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Congratulations 2024 Spotlight Honorees

Kudos to all of our 2024 community stars! 🎉

Meet the Top 10 leaders for December

Congratulations to our December super stars! 🥳

Start Your Super User Journey Pt 2

Join the ranks of our community heros! 🦹

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,897 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,573 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans