Search
  • Marek Vavrovic

Append files with different culture format

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"



4 views0 comments