Microsoft Cloud for Sustainability | MSM, SDSF,...

Answered

Data import error with date and currency columns

(2) Share

Report

Posted on by Lea Kraus

141

Hi team,

currently we encounter an import issue with date and currency columns when using the power query connector within MSM.

Following scenario:

The raw data come in an excel file and contain a date and a currency column, both are formatted in US formats -> mm/dd/yyy and 1,500.00 €.

I'm using the MSM in german language and when transforming the excel in PQ, both date and currency column are already displayed in german format without doing any adaptions (dd.mm.yyyy and 1.500,00€). Then, when I try to import the data into the MSM I get following error message for the date column:

The currency column gets imported but also not in the right format and the values are not correct at all (e.g. the value 970 000 € gets imported as 97 000 000 €).

I already tried to use the locale function within PQ or changing the format first to text and then to date. But I still get the error message.

The current (dirty) workaround:

I split the date column in three columns (day, month and year) using a delimiter and then concatenate it again to month-day-year. But that does not seem stable at all and also does not work for the currency column.

Any idea what the issue is and how to solve it? Which date and currency format does the MSM expect when importing via PQ connection?

Thanks and cheers,

Lea

Categories:

Sustainability Manager

All responses (2)

Answers (1)

Lea Kraus 141 on at

Like (0)

Report

Data import error with date and currency columns

Hi Vanya,

so the environment region where the MSM is installed in Europe.

I changed the display language of the MSM to English via personalization settings. And within PQ, I did not change the column formats -> both had the initial data type "text" and I did not change that.

And with this settings, the values actually got imported in the right format. I'm not sure if something else changed or if it was just the language change, but it seems to work now for both, the currency and the date.

But anyways, it would be great if the user locale could be changed.

Cheers,

Lea
Verified answer

Ivan (Vanya) Kashperuk on at

Like (1)

Report

Data import error with date and currency columns

So the backend logic we have currently uses the language/format of the region it's running in, so in your case, it looks like it is expecting US format.

However, Power Query seems to save it transformed to the format of you as the user, which is German, even though the original source file has US format

Easiest workaround is to change your language. But we'll think of how we can address it taking into account the user locale as well.

Community site session details

Data import error with date and currency columns

Helpful resources

Quick Links

Ramesh Kumar – Community Spotlight

Congratulations to the June Top 10 Community Leaders!

Announcing the Engage with the Community forum!

Leaderboard > Microsoft Cloud for Sustainability | MSM, SDSF, EID

Featured topics

Product updates

Community site session details

Data import error with date and currency columns

Helpful resources

Quick Links

Ramesh Kumar – Community Spotlight

Congratulations to the June Top 10 Community Leaders!

Announcing the Engage with the Community forum!

Subscribe to this forum!

Select categories

Leaderboard > Microsoft Cloud for Sustainability | MSM, SDSF, EID

Featured topics

Product updates