excelpowerquery

Excel PowerQuery. Negative numbers missing hyphen-minus symbol

I have multiple Excel files that I am trying to combine via PowerQuery. These files are generated by ERP system and have the column "Expenditures" with positive/negative numbers in default format that displays negative numbers in red font without a minus symbol.

When combining them, Power Query transforms all numbers in the column "Expenditure" into positive numbers.

From what I understand Power Query does not natively detect font colors so I cannot Create a Conditional Column to Detect Red Font. Also, I cannot create a Conditional Column to Indicate that this number should be negative since in the source file there are no specific indicators. I can solve this issue by manually changing the cell format in the source Excel file so that negative numbers are displayed with a minus sign, but I am wondering if there is a way to solve this issue on Power Query side without having to manually change cells format (due to a big number of files).

PQ code:

let
    Source = Folder.Files("C:\Users\yevgen.matiukha\..."),
    #"Filtered Rows" = Table.SelectRows(Source, each ([Name] <> "desktop.ini")),
    #"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"}),
    #"Removed Errors1" = Table.RemoveRowsWithErrors(#"Removed Other Columns1", {"Transform File"}),
    #"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Errors1", "Transform File", Table.ColumnNames(#"Transform File"(#"Sample File")))
in
    #"Expanded Table Column1"

Thank you for any advice!

Solution

  • The problem you are running into is due to an incompatibility between *.xls files and Power Query. This was reported at least four years ago to Microsoft Power BI team, so it probably won't get fixed.

    Several possible solutions:

    I know of no way to solve this using Power Query alone.

    I'd favor the first solution, as this will preserve the formatting from the ERP.