I have couple of files with different date and value formats. Will use a function which will format them correctly...
Source files:
There are 4 files in the folder. Some dates are formatted as DD/MM/YYYY other as MM/DD/YYYY. And also some fields with Amount having " . " other " , " as a decimal indicator.
Step1: I have created a table with Country codes and moved the table into power query. Get one file into power query, use the code to build a function.
CultureTbl is the table containing a correct culture for each country file I have inside the folder.
fxCulture: I have just imported one file from the folder and I am going to rewrite the code so it will be used as a function to extract the rest of the files.
Step2: In power query, click on the fxCulture table and open up Advanced Editor. Replace the highlighted part of the code with Table. In the last step : Table.TransformColumnTypes, the 3rd argument is culture as text. So I am using variable Culture for this. You don't have to specify the data types (binary, text) it is optional.
Step3: the function is ready, I am loading the rest of the files form a folder.
Step4: Extract text between delimiters to get the country names.
Step5: Merge queries as new.
Step6: The merge step has created a table. Keep just Culture column.
Step7: Highlight Content, Name, Culture columns and remove other columns.
Step8: Now it's time to use the function. Go to Add Column> Invoke Custom Function. The variables form the function will look for two columns. I am going to map them to Content (Table variable) and Culture (Culture variable)
How you can see form this picture, dates and amounts are coming in a correct format. I am going to remove Content and Culture columns.
Final table:
Now I can add more files into that folder, refresh the table and all the files will be loaded correctly with no date or value issues.
After refresh:
EXAMPLE_2
Power Query using Default Regional Settings. I have created a table with 3 types of dates and 2 types of values. I will fix the date and value problems with Using Local... option.
DD/MM/YYYY=TEXT(DAY(A2),"00")&"/"&TEXT(MONTH(A2),"00")&"/"&YEAR(A2)
ISO Date=YEAR(A2)&TEXT(MONTH(A2),"00")&TEXT(DAY(A2),"00")
US Sales values are formatted as number and France Sales are formatted as text.
Step1: Load the table into Power Query. I have US in the Regional Settings and just the first date format was detected correctly. If I would change my Regional Settings to United Kingdom, both MM/DD/YYYY and DD/MM/YYYY will be detected correctly because United Kingdom Regional Settings can do more magic then US settings. But I am not going to prove this I just show you how to fix this problem with dates and values for sales.
Step2: click on the data type > Using Locale
Step3: choose data type Date, Locale: United Kingdom or France...(what works)
Step4: ISO Date: If is formatted as number change it to text first and then from text to date.
Step5: France Sales col. is formatted as text. If I change the data type to decimal it returns an error. You can fix it by Using Local...
Final table:
M Code:
let
Source = Excel.CurrentWorkbook(){[Name="FactSales"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{" MM/DD/YYYY", type date}}),
#"Changed Type with Locale" = Table.TransformColumnTypes(#"Changed Type", {{"DD/MM/YYYY", type date}}, "en-GB"),
#"Changed Type1" = Table.TransformColumnTypes(#"Changed Type with Locale",{{"ISO Date", type date}, {"US Sales", type number}}),
#"Changed Type with Locale1" = Table.TransformColumnTypes(#"Changed Type1", {{"France Sales", type number}}, "fr-FR")
in
#"Changed Type with Locale1"