Expand column without weird characters

Power Query
1

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

2

@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
3

Here’s a video on bulk replace special characters.

image

4

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

5

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

6

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

7

@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.

image
image1659×374 53.4 KB

  • Brian
8

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?

9

@pedroccamara,

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

  • Brian
1 Like
10

Thank you very much @BrianJ
I will look at it