cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Tanmaub8055
Regular Visitor

Expression.Error: We cannot apply operator & to types Number and Text

Expression.Error: We cannot apply operator & to types Number and Text. Details: Operator=& Left=394838 Right=4274030


let
Source = SharePoint.Files("https://sharepoint.com/PowerBiDashboards", [ApiVersion = 15]),
#"Filtered Rows" = Table.SelectRows(Source, each Text.StartsWith([Name], "VG Global OTD") and [Extension] = ".xlsx"),
#"Filtered Hidden Files1" = Table.SelectRows(#"Filtered Rows", each [Attributes]?[Hidden]? <> true),
#"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File (2)", each #"Transform File (2)"([Content])),
#"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
#"Expanded Table Column1" = Table.ExpandTableColumn(#"Renamed Columns1", "Transform File (2)", Table.ColumnNames(#"Transform File (2)"(#"Sample File (2)"))),
#"Added Custom" = Table.AddColumn(#"Expanded Table Column1", "PK_Plant", each [Site]&[Order Type]&[Product Line]),
#"Split Column by Delimiter" = Table.SplitColumn(Table.TransformColumnTypes(#"Added Custom", {{"Line Number", type text}}, "en-US"), "Line Number", Splitter.SplitTextByDelimiter(".", QuoteStyle.None), {"Line Number.1", "Line Number.2"}),
#"Added Custom1" = Table.AddColumn(#"Split Column by Delimiter", "Date", each [#"ScheduleShipDate/Promise Date"]),
#"Split Column by Delimiter2" = Table.SplitColumn(Table.TransformColumnTypes(#"Added Custom1", {{"Date", type text}}, "en-US"), "Date", Splitter.SplitTextByDelimiter("/", QuoteStyle.None), {"Date.1", "Date.2", "Date.3"}),
#"Added Custom2" = Table.AddColumn(#"Split Column by Delimiter2", "Date", each [Date.1]&"/"&[Date.2]&"/"&[Date.3]),
#"Changed Type3" = Table.TransformColumnTypes(#"Added Custom2",{{"Date", type date}}),
#"Added Custom3" = Table.AddColumn(#"Changed Type3", "Month", each Date.Month([Date])),
#"Changed Type4" = Table.TransformColumnTypes(#"Added Custom3",{{"Month", type text}}),
#"Added Custom4" = Table.AddColumn(#"Changed Type4", "Item_Order_Line", each [Item Number]&[Order Number]&[Line Number.1]&[Month]),
#"Added Conditional Column" = Table.AddColumn(#"Added Custom4", "Early", each if [#"Early/Late/On Time"] = "Early" then 1 else 0),
#"Added Conditional Column1" = Table.AddColumn(#"Added Conditional Column", "On Time", each if [#"Early/Late/On Time"] = "On Time" then 1 else 0),
#"Added Conditional Column2" = Table.AddColumn(#"Added Conditional Column1", "Late", each if [#"Early/Late/On Time"] = "Late" then 1 else 0),
#"Changed Type" = Table.TransformColumnTypes(#"Added Conditional Column2",{{"Early/Late/On Time", type text}, {"Is Partial?", type text}, {"Early", Int64.Type}, {"On Time", Int64.Type}, {"Late", Int64.Type}, {"Month", type text}, {"Date.3", Int64.Type}, {"Date.2", Int64.Type}, {"Date.1", Int64.Type}, {"Item_Order_Line", type text}, {"Item Number", type text}, {"Order Number", type text}, {"Site", type text}, {"Order Type", type text}, {"Product Line", type text}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Item_Order_Line"}, {{"Early", each List.Sum([Early]), type number}, {"On time", each List.Sum([On Time]), type number}, {"Late", each List.Sum([Late]), type number}}),
#"Added Conditional Column3" = Table.AddColumn(#"Grouped Rows", "Early_Late_On Time", each if [Late] > 0 then [Late] else if [Early] > 0 then [Early] else [On time]),
#"Added Conditional Column4" = Table.AddColumn(#"Added Conditional Column3", "V_Early_Late_On Time", each if [Late] > 0 then "Late" else if [Early] > 0 then "Early" else "On Time")
in
#"Added Conditional Column4"

4 REPLIES 4
v-yadongf-msft
Community Support
Community Support

Hi @Tanmaub8055 

 

It might be a data type issue. Note that the Combination operator (&) is suitable for the following scenarios:

1)Text concatenation

2)List concatenation

3)Record merge

 

For more information, please refer to:

Operators - PowerQuery M | Microsoft Docs

 

So, if the data types are Number and Text, you will get the following error: We cannot apply operator & to types Number and Text.

vyadongfmsft_0-1659433619949.png

 

The solution is very simple, please convert all data types to text.

vyadongfmsft_1-1659433619952.png

 

And you can use operator (&) normally.

vyadongfmsft_2-1659433619953.png

 

Best Regards,

Yadong Fang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

AlexisOlson
Super User
Super User

I'd guess the issue is in one of these places:

 "PK_Plant", each [Site]&[Order Type]&[Product Line]
"Item_Order_Line", each [Item Number]&[Order Number]&[Line Number.1]&[Month]

Check that each of these columns is a text type prior to attempting to concatenate them as text.

ribisht17
Super User
Super User

@Tanmaub8055 

 

Here is a similar blog or post to follow Solved: Expression.Error: We cannot apply operator - to ty... - Microsoft Power BI Community

 

HINT

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.

 

Regards,

Ritesh

Mark my post as a solution if it helped you| Munde and Kudis (Ladies and Gentlemen) I like your Kudos!! !!
My YT Channel Dancing With Data !! Connect on Linkedin !!Power BI for Tableau Users 

technolog
Super User
Super User

You just need to find string where is mistake. And change type of column from text to int



____________
Please join the Power BI User Group if you need help with dashboard design and usability
https://community.powerbi.com/t5/Power-BI-UX-UI-User-Group/gh-p/PowerBIUXUIUserGroup

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.

Top Solution Authors
Top Kudoed Authors