web
You’re offline. This is a read only version of the page.
close
Skip to main content
Community site session details

Community site session details

Session Id :
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 166
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
I have the same question (0)
  • CU03091316-0 Profile Picture
    CU03091316-0 166 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 166 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 166 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 2,346 Super User 2025 Season 2 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

News and Announcements

Join experts in person to explore low code and AI agents at the Power Platform Community Conference sponsored by Microsoft
Our Community Code of Conduct has been updated!

Quick Links

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Andrés Arias – Community Spotlight

We are honored to recognize Andrés Arias as our Community Spotlight honoree for…

Congratulations to the August Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > Customer experience | Sales, Customer Insights, CRM

#1
Daniyal Khaleel Profile Picture

Daniyal Khaleel 130

#2
DAnny3211 Profile Picture

DAnny3211 97

#3
Abhilash Warrier Profile Picture

Abhilash Warrier 66 Super User 2025 Season 2

Last 30 days Overall leaderboard

Featured topics

Hide selecting Contact for potential customer when creating quote

Product updates

Dynamics 365 release plans