import a folder with xml files without weird characters

[pedroccamaraDBI]

Hello all

I know that if i import just 1 file xml, even with weird characters, i would add the 1252 code. But what if i want to import all xml files from a folder? I can't seem to find a way to "clean" all those characters. Could it be in file attributes? Which one?

Thanks in advance

Pedro

[fhill · Accepted solution]

Try inserting the steps you need in the 'Transform Sample Files...' Data Sheet in the 'Transform File from...' Folder in Query Editor.

I had a situation once, where I had to Un-Pivot some data AFTER the promote header step on EVERY File, but once Power BI Merged all the files together, it was impossible (the way I needed it) to Un-Pivot afterwards.  Adding the step here, applies that step to EVERY File Before they are all merged together.

FOrrest

[pedroccamaraDBI]

Such an awesome tip @fhill 
I didn't even know that we could "mess" with those files....AWESOME!
Thank you so much

[fhill]

Glad it worked!  Thank you for the solution mark!

FOrrest

[parry2k]

@pedroccamaraDBI you just need to the folder as a data source to read files from it and then transform each file, you can create a function to pass the content of each file to this function. similar to what you will do if you are reading multiple excel files from a folder.

Check this link for reference

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[pedroccamaraDBI]

Thanks for your answer @parry2k but i'm not sure that i understood your answer or if you undertstood my question.
It's very common for me to import files from a folder, any kind, excel or csv or even xml. But the thing about some xml files is that some characters of some columns are weird...with this character � instead "á" for example.
New source - More - Folder  and then i choose the folder and press Transform Data because i want to choose which files to work with. And the then i just click on content column. This is the code so far for a folder with xml files.

let
Source = Folder.Files("C:\Users\Pedro\...\Sources"),
#"Filtered Rows" = Table.SelectRows(Source, each ([Extension] = ".xml")),
#"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"}),
#"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File", Table.ColumnNames(#"Transform File"(#"Sample File"))),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Source.Name", type text}, {"NumberOfEntries", Int64.Type}, {"TotalDebit", type number}, {"TotalCredit", type number}, {"Invoice", type any}})
in
#"Changed Type"

Then i've import just one xml file and, because i can

i've change where it says file origin to 1252 and the M code is like this:

let
Source = Xml.Tables(File.Contents("C:\Users\Pedro\...\Sources\SAFT_DFK_01-01-2020_31-12-2020.xml"), null, 1252),
Table2 = Source{2}[Table],
Table0 = Table2{0}[Table],
#"Changed Type" = Table.TransformColumnTypes(Table0,{{"NumberOfEntries", Int64.Type}, {"TotalDebit", type number}, {"TotalCredit", type number}})
in
#"Changed Type"

My question is, what can i do to import from a folder of xml files and "add that" 1252 number?
Thanks in advance