Forum Discussion
Expression.Error: We cannot apply operator - to types Text and Text.
Hi BI Community,
This is my first time posting here. Need help with a lingering issue.
When i pivoted a column to create new columns, one of my new columns "Reason Code" contains error with:
Expression.Error: We cannot apply operator - to types Text and Text.
Details:
Operator=-
Left=For Example
Right=For Example
I use "for example" a cell content. Never included the "-" difference operator in my text at all. What is the root cause and solution? Thanks in advance!
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
4 Replies
Hi Anonymous
I believe you use the "-" operator somewhere, no place for magic here
Pick your query, then open advanced editor and copy full text here in order to help you
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).
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 Thingsclump ,
Indeed The list.sum caused the error when i pivoted the column. I realize the that the column i pivoted has both numberical and text values. I changed from sum to "dont aggregate" and now the model works fine. Not concerned with not summing my like-like values as the raw data are summations already from previous models.
Big thanks for your help!
