Really hoping someone can help here. I've been trying to fix this for a week and just cannot do it.
I've got a dataflow consolidating all files in one sharepoint folder, and all of a sudden I started getting this error:
Error: Expression.Error: We cannot convert the value "[Binary]" to type Binary. .. RootActivityId = 4510d2e6-b788-4aed-8f7f-ff31d2469ca2.Param1 = Expression.Error: We cannot convert the value "[Binary]" to type Binary. Request ID: a5e8e233-9403-44c8-af56-f3ade9f2db0c.
I took out all the files from the folder that changed since the error started, but still no luck - which leads me to think that the issue is not with the files.
let
#"Filtered hidden files" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),
#"Invoke custom function" = Table.Buffer(Table.AddColumn(#"Filtered rows", "Transform file", each #"Transform file"([Content]))),
#"Expanded Transform file" = Table.ExpandTableColumn(#"Invoke custom function", "Transform file", {"BU ID", "Account & Name", "Attribute", "Value"}, {"Transform file.BU ID", "Transform file.Account & Name", "Transform file.Attribute", "Transform file.Value"}),
#"Removed other columns" = Table.SelectColumns(#"Expanded Transform file", {"Transform file.BU ID", "Transform file.Account & Name", "Transform file.Attribute", "Transform file.Value", "Name"}),
#"Transform columns" = Table.TransformColumnTypes(#"Removed other columns", {{"Transform file.BU ID", type text}, {"Transform file.Account & Name", type text}, {"Transform file.Attribute", type text}, {"Transform file.Value", type text}}),
#"Replace errors" = Table.ReplaceErrorValues(#"Transform columns", {{"Transform file.BU ID", null}, {"Transform file.Account & Name", null}, {"Transform file.Attribute", null}, {"Transform file.Value", null}}),
#"Added custom" = Table.AddColumn(#"Replace errors", "Forecast Type", each Text.BeforeDelimiter([Transform file.Attribute], "|")),
#"Added custom 1" = Table.AddColumn(#"Added custom", "Actual/Forecast", each Text.BetweenDelimiters([Transform file.Attribute], "|", "/")),
#"Added custom 2" = Table.AddColumn(#"Added custom 1", "Month", each Text.AfterDelimiter([Transform file.Attribute], "/")),
#"Changed column type" = Table.TransformColumnTypes(#"Added custom 2", {{"Month", Int64.Type}}),
#"Changed column type 1" = Table.TransformColumnTypes(#"Changed column type", {{"Month", type date}}),
#"Merged queries" = Table.NestedJoin(#"Changed column type 1", {"Transform file.Account & Name"}, #"Forecasting Group Mapping", {"Forecasting Group"}, "Forecasting Group Mapping", JoinKind.LeftOuter),
#"Expanded Forecasting Group Mapping" = Table.ExpandTableColumn(#"Merged queries", "Forecasting Group Mapping", {"Account Number"}, {"Forecasting Group Mapping.Account Number"}),
#"Filtered rows 1" = Table.SelectRows(#"Expanded Forecasting Group Mapping", each [Forecasting Group Mapping.Account Number] <> "Exclude"),
#"Added custom 3" = Table.AddColumn(#"Filtered rows 1", "Account Type Step", each if [Forecasting Group Mapping.Account Number] = "Contract Type" or [#"Transform file.Account & Name"]= "Contract Status" then "Masterdata" else "Financials"),
#"Removed columns" = Table.RemoveColumns(#"Added custom 3", {"Transform file.Attribute", "Transform file.Account & Name"}),
#"Renamed columns" = Table.RenameColumns(#"Removed columns", {{"Name", "File Name"}, {"Transform file.Value", "Value"}, {"Transform file.BU ID", "BU ID"}, {"Forecasting Group Mapping.Account Number", "Account Number"}}),
#"Transform columns 1" = Table.TransformColumnTypes(#"Renamed columns", {{"Forecast Type", type text}, {"Actual/Forecast", type text}, {"Account Type Step", type text}}),
#"Replace errors 1" = Table.ReplaceErrorValues(#"Transform columns 1", {{"Forecast Type", null}, {"Actual/Forecast", null}, {"Account Type Step", null}})
in
#"Replace errors 1"
