Expand column without weird characters

Hello all
I have this expand line :

= Table.ExpandTableColumn(#“Expanded DocumentTotals”, “Line”, {“LineNumber”, “ProductCode”, “ProductDescription”, “Quantity”, “UnitPrice”, “CreditAmount”, “SettlementAmount”}, {“LineNumber”, “ProductCode”, “ProductDescription”, “Quantity”, “UnitPrice”, “CreditAmount”, “SettlementAmount”})

The product code and ProductDescription are like this picture attached, weird characters, because we have letters like á, ã, ç and so on…
Can this be solved?

Capture3

Thank you all
Pedro

@pedroccamara,

One possibility is to check/change the encoding method in your Source step.

Check out the following thread from the Microsoft forum and see if this solves the problem.

  • Brian

Here’s a video on bulk replace special characters.

image

Guys,
Thank you so much 4 your answer.
I didn’t mentioned that I’m working with XML files (saft files) but from folder, not individually. I mean they’re more than 1.
I will look at both solutions later, for sure.
Thank you very much
Pedro

Hi @pedroccamara

What I could see per your screenshot you can use the options as shared by @BrianJ or @Melissa.
If the order is same you can use delimiter option as well in query editor where you can get a separate columns for the special characters.
If the order is different then I would suggest 1st try and resolve this in the source file if possible else you have to replace these in query editor.

Thanks,
Ankit

Hey guys, i’m sorry to say but it’s not working any solution. First, i can’t make a list because today is this and tomorrow maybe that, you know. Now, changing the encoding might be a good idea but don’t know how since i have nothing about encoding in my m code. Maybe also cose i’m trying to export a folder with several xml files, Would it b better to have just 1 file?
Here’s my M code:

let
Source = Folder.Files(“C:\Users\Pedro\Folder1\Folder2\Sources\SAFT”),
#“Removed Other Columns” = Table.SelectColumns(Source,{“Name”, “Content”}),
#“Filtered Hidden Files1” = Table.SelectRows(#“Removed Other Columns”, each [Attributes]?[Hidden]? <> true),
#“Invoke Custom Function1” = Table.AddColumn(#“Filtered Hidden Files1”, “Transform File (3)”, each #“Transform File (3)”([Content])),
#“Removed Columns” = Table.RemoveColumns(#“Invoke Custom Function1”,{“Content”}),
#“Expanded Transform File (3)” = Table.ExpandTableColumn(#“Removed Columns”, “Transform File (3)”, {“Invoice”}, {“Invoice”}),
#“Expanded Invoice” = Table.ExpandTableColumn(#“Expanded Transform File (3)”, “Invoice”, {“InvoiceNo”, “DocumentStatus”, “InvoiceDate”, “InvoiceType”, “CustomerID”, “Line”, “DocumentTotals”}, {“InvoiceNo”, “DocumentStatus”, “InvoiceDate”, “InvoiceType”, “CustomerID”, “Line”, “DocumentTotals”}),
#“Expanded DocumentStatus” = Table.ExpandTableColumn(#“Expanded Invoice”, “DocumentStatus”, {“InvoiceStatus”}, {“InvoiceStatus”}),

What i don’t know is where to add the encoding instruction

Thanks for all your help
Pedro

@pedroccamara,

Thanks for the clarification - knowing the file is XML is helpful. Here are two more approaches to try - one recoding your file as unicode, and one changing the source settings in PQ.

  • Brian

Hey @BrianJ
Now i know i’m gonna fix this for sure :slight_smile:
I didn’t understand the first example, what he did in the code. And the 2nd example, in the source line code, i only have this = Folder.Files(“C:\Users\Pedro\Folder1\Folder2\Sources\SAFT”)
I’ve tried to “add” something like Encoding = 65000 but it gave me error…
Any idea?

@pedroccamara,

Recoding your XML as Unicode is something you’d need to do before importing it into PQ. Here’s a bit more info:

http://w3schools.sinsixx.com/xml/xml_encoding.asp.htm

  • Brian
1 Like

Thank you very much @BrianJ
I will look at it