topic Re: Expression.Error: We cannot apply operator & to types Number and Text in DAX Commands and Tips

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

Tanmaub8055 — Sun, 31 Jul 2022 10:46:59 GMT

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"

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

technolog — Sun, 31 Jul 2022 11:21:55 GMT

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

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

ribisht17 — Sun, 31 Jul 2022 16:28:11 GMT

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

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

AlexisOlson — Sun, 31 Jul 2022 18:12:22 GMT

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.

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

v-yadongf-msft — Tue, 02 Aug 2022 09:49:10 GMT

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.

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

And you can use operator (&) normally.

^{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.}