Problem in conversion Different date format in the same column

Fabio_Ghirardi · March 22, 2021, 2:42pm

Hi
I have a pretty wide database of file coming from different countries (different data format - see an example in attachment). I have consolidated all these files (126 csv files = 5 MB) in a big power BI table as follow

upload CSV file organized x year and country
Consolidated per country --> change date type using locale…
Consolidated in a unique table (the only one imported in my Power BI model)

I noticed that this approach slow down the model and I managed to let it work much more efficiently by directly consolidating all the files in a unique table.

I have not been able to get useful result out of the second approach because if I use the “change type using locale” in a column with multiple date format, I loose a lot of data as “Error” due to data format conversion
Is there a way to convert 1 column with different date format in a unique standardize date column?

As additional note I have a column which identify the different country (ie DE, IT, FR, …) and can be eventually used as reference column to identify the source date format (for example in case of a “IF…else” approach)

Thanks a lot in advanced for your support
Best regards
Fabio

CSV with different date format.xlsx (12.2 KB)

Matthias · March 22, 2021, 4:46pm

@Fabio_Ghirardi you could add a table with the locales for each country:

That Locale table you can merge with the data query and then use the Locale for the transformation of each file or table. CSV with different date format.xlsx (23.4 KB) :

let
    Source = DataTables,
    #"Merged Queries" = Table.NestedJoin(Source, {"Name"}, Locale, {"Country"}, "Locale", JoinKind.LeftOuter),
    #"Expanded Locale" = Table.ExpandTableColumn(#"Merged Queries", "Locale", {"Locale"}, {"Locale"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Locale", "Custom", each Table.TransformColumnTypes(
Table.TransformColumnTypes(
Table.ReplaceValue(
Table.SplitColumn([Content], "Column1", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Date", "Column1.2", "Column1.3", "Column1.4", "Column1.5", "Column1.6", "Column1.7"}),
" UTC","",Replacer.ReplaceText,{"Date"}),
{{"Date", type datetime}}, [Locale]),
{{"Date", type date}})),
    #"Expanded Custom" = Table.Combine(#"Added Custom"[Custom])
in
    #"Expanded Custom"

BrianJ · March 22, 2021, 5:05pm

@Matthias,

- Wonderfully creative solution.

Really been enjoying following your posts on the forum recently. Great stuff!

– Brian

Fabio_Ghirardi · March 22, 2021, 5:49pm

HI Matthias

Thanks a lot for your quick support. I really appreciate I will try it as soon as I can and let you know

Thanks again for your support and have a great day ahead
FG

Fabio_Ghirardi · March 22, 2021, 9:38pm

Hi Matthias

I have tried your solution but I am pretty rookie with power BI and less than that with M language.
I got an error message (see attached screenshot) can you be so kind to help me a little bit further :-)!

Thanks a lot for your help
I wish you a lovely night
Fabio

Matthias · March 22, 2021, 11:49pm

Ciao Fabio,

I accessed the tables I created in your example file and used then the resulting table as source for the Data query - that approach works fine if you have all data already consolidated per country.

let
    Source = Excel.CurrentWorkbook(),
    #"Filtered Rows" = Table.SelectRows(Source, each ([Name] <> "Locale"))
in
    #"Filtered Rows"

But you want to access the 126 csv, so you best access the Folder, then filter for all relevant csv files, and then Combine the Binary (by hitting the double arrow on the Content column). That should automatically create a number of helper queries and one where all csv are combined and expanded. You don’t want to have it expanded, so delete all steps after “Removed Other Columns1” to get a table of file names (Source.Name) and tables (Transform File). That will look similar to the table I had above and could be your starting point.

Fabio_Ghirardi · March 24, 2021, 8:04am

HI Matthias

Thanks a lot. I start understanding the syntax behind. I appreciate your clarification

I wish you a great day ahead
FG

EnterpriseDNA · March 25, 2021, 3:48am

Hi @Fabio_Ghirardi, did the response provided by @Matthias help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query. Thanks!

Fabio_Ghirardi · March 25, 2021, 4:01pm

Hi Matthias
I am doing some step forward… I think :-)! but I am probably missing the last step:
NO I have 1 single table with the DATE in local format and the reference column for the country.
See example below:

What Is not yet clear is how can I say
If “source.name” = “DE” --> “Changed Type with Locale” = Table.TransformColumnTypes(#“Split Column by Position”, {{“Column1.1”, type date}}, “de-DE”
else
If “source.name” = “IT” --> “Changed Type with Locale” = Table.TransformColumnTypes(#“Split Column by Position”, {{“Column1.1”, type date}}, “it-IT”
etc etc

I have tried with formula below but despite I did not get any syntax error I can not achieve what I want

#“Changed Type with Locale” = Table.TransformColumnTypes(#“Split Column by Position”, {{“Column1.1”, type date}}, if [Source.Name] = “DE” then “de-DE”** else if [Source.Name] = “IT” then “it-IT” else if [Source.Name] = “FR” then “fr-FR” else if [Source.Name] = “ES” then “es-ES” else if [Source.Name] = “UK” then “en-GB” else “sv-SE”))

Do you have any further suggestion

Thanks again in advanced
FG

Matthias · March 25, 2021, 8:14pm

Fabio, when you button your shirt, it is the first button which is important not the last.

But you have several options. Here are two:

The way you have it now, the easiest for you would be to go to each separate table and do it manually and then append. There are just 6 cases you have.

Above I had this suggestion and I think the attached code should realize it for you. And of course you can adapt the steps:

you best access the Folder, then filter for all relevant csv files, and then Combine the Binary (by hitting the double arrow on the Content column). That should automatically create a number of helper queries and one where all csv are combined and expanded. You don’t want to have it expanded, so delete all steps after “Removed Other Columns1” to get a table of file names (Source.Name) and tables (Transform File). That will look similar to the table I had above and could be your starting point.

let
    Source = Folder.Files("YourFolderPath\YourFolderName"),
    #"Filtered Rows" = Table.SelectRows(Source, each ([Extension] = ".csv")),
    #"Filtered Hidden Files1" = Table.SelectRows(#"Filtered Rows", each [Attributes]?[Hidden]? <> true),
    #"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File", each #"Transform File"([Content])),
    #"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
    #"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File"}),

    #"Merged Queries" = Table.NestedJoin(#"Removed Other Columns1", {"Source.Name"}, Locale, {"Country"}, "Locale", JoinKind.LeftOuter),
    #"Expanded Locale" = Table.ExpandTableColumn(#"Merged Queries", "Locale", {"Locale"}, {"Locale"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Locale", "Custom", each Table.TransformColumnTypes(
Table.TransformColumnTypes(
Table.ReplaceValue(
Table.SplitColumn([Transform File], "Column1", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Date", "Column1.2", "Column1.3", "Column1.4", "Column1.5", "Column1.6", "Column1.7"}),
" UTC","",Replacer.ReplaceText,{"Date"}),
{{"Date", type datetime}}, [Locale]),
{{"Date", type date}})),
    #"Expanded Custom" = Table.Combine(#"Added Custom"[Custom])
in
    #"Expanded Custom"

Fabio_Ghirardi · March 26, 2021, 9:29am

HI Matthias

I got it :-)! thanks again for your help It was really usefull

I wish you a great week end
FG