Share
ReportHi 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:
All responses (
Answers (
