Forum Discussion
Expression.Error: We cannot apply operator - to types Text and Text.
Hi Anonymous
Can you share a screenshot that shows all columns? It looks like List.Sum is causing issue in Pivot. May be PQ is throwing error trying to do a mathematical operation on text field.
If possible share the PQ code after creating input table in PQ along with remaining steps. If not, share excel file removing sensitive information. This will be good for troubleshooting as well.
Mark this solution as accepted if it solves your issue.
Thanks
thingsclump
hi az38,
Many thanks for the prompt reply. here is what the advacned editor says:
let
Source = Excel.Workbook(File.Contents("C:\Users\usawinna\Danone\WONGPANICHLERT Saranya - ISEA_SPT\03_KPI Performance Dashboard\D2D KPI Working FIle.xlsx"), null, true),
FlashCall_KPIs_Table = Source{[Item="FlashCall_KPIs",Kind="Table"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(FlashCall_KPIs_Table,{{"Category", type text}, {"Sub-Category", type text}, {"Code", type text}, {"Data Type (For BI config.)", type text}, {"KPI", type text}, {"Input By", type text}, {"1/1/2021", type any}, {"2/1/2021", type any}, {"3/1/2021", type number}, {"4/1/2021", type number}, {"5/1/2021", type number}, {"6/1/2021", type any}, {"7/1/2021", type number}, {"8/1/2021", type number}, {"9/1/2021", type number}, {"10/1/2021", type number}, {"11/1/2021", Int64.Type}, {"12/1/2021", Int64.Type}, {"YTD", type number}}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Category", "Sub-Category", "Code", "Data Type (For BI config.)", "KPI", "Input By"}, "Attribute", "Value"),
#"Renamed Columns" = Table.RenameColumns(#"Unpivoted Other Columns",{{"Attribute", "Date"}}),
#"Duplicated Column" = Table.DuplicateColumn(#"Renamed Columns", "Value", "Value - Copy"),
#"Renamed Columns1" = Table.RenameColumns(#"Duplicated Column",{{"Value - Copy", "Value 2"}, {"Value", "Value Summary"}}),
#"Pivoted Column" = Table.Pivot(#"Renamed Columns1", List.Distinct(#"Renamed Columns1"[#"Data Type (For BI config.)"]), "Data Type (For BI config.)", "Value 2", List.Sum),
#"Changed Type1" = Table.TransformColumnTypes(#"Pivoted Column",{{"Value Summary", type number}, {"Raw Data", type number}, {"Calculation %", Percentage.Type}, {"Calculation ABS", type number}, {"Reason Code", type text}}),
#"Filtered Rows" = Table.SelectRows(#"Changed Type1", each ([KPI] = "Factory related Consumer Complaints(Reason)" or [KPI] = "Number of NC (Reason)" or [KPI] = "Number of SNC (Reason)" or [KPI] = "Supply Chain related Consumer Complaints (Reason)")),
#"Reason Code" = #"Filtered Rows"{2}[Reason Code]
in
#"Reason Code"
A few pics for reference before and after error occurs:
Error occurs as soon as i pivot one of my columns (creating a "reason" field).