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

[Tanmaub8055]

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"

[Anonymous · Accepted solution]

Hi @Tanmaub8055,

I think this issue should be related to the 'Add custom2' steps, current you can't directly use the '&' operator to concatenate different types of values. You need to convert these values to text first:

#"Added Custom2" = Table.AddColumn(
    #"Split Column by Delimiter2",
    "Date",
    each
        Text.From([Date.1]) & "/" & Text.From([Date.2]) & "/" & Text.From([Date.3])
)

Regards,

Xiaoxin Sheng