Evaluation error in dataflow

[Jebilaya]

Dataflow was working fine and then 4 additional excel files added to the sharepoint file and now dataflow evaluation errors when trying to view.  Even if the added files are deleted still cannot load the dataflow.

There's quite a lot going on in the query; is this unable to process?  Any ideas or pointers?

---------- Message ----------
We're sorry, an error occurred during evaluation.

---------- Session ID ----------
ff5f5250-c856-4f50-b63e-550bd0ebd1b4

---------- Request ID ----------
1a9a7eb7-a5ce-4f09-8d69-de16b5fbcaca

[Jebilaya]

Advanced editor code

let

  Source = SharePoint.Files("https://wincanton.sharepoint.com/sites/HDAnalytics", [ApiVersion = 15]),

  #"Filtered rows" = Table.SelectRows(Source, each [Folder Path] = "https://wincanton.sharepoint.com/sites/HDAnalytics/Shared Documents/HDx Sources/Delivery Outcome Report/"),

  #"Filtered hidden files" = Table.SelectRows(#"Filtered rows", each [Attributes]?[Hidden]? <> true),

  #"Invoke custom function" = Table.AddColumn(#"Filtered hidden files", "Transform file (4)", each #"Transform file (4)"([Content])),

  #"Renamed columns" = Table.RenameColumns(#"Invoke custom function", {{"Name", "Source.Name"}}),

  #"Removed other columns" = Table.SelectColumns(#"Renamed columns", {"Source.Name", "Transform file (4)"}),

  #"Expanded table column" = Table.ExpandTableColumn(#"Removed other columns", "Transform file (4)", Table.ColumnNames(#"Transform file (4)"(#"Sample file (4)"))),

  #"Changed column type" = Table.TransformColumnTypes(#"Expanded table column", {{"Source.Name", type text}, {"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}, {"Column10", type text}, {"Column11", type text}, {"Column12", type text}, {"Column13", type text}, {"Column14", type text}, {"Column15", type text}, {"Column16", type text}, {"Column17", type text}, {"Column18", type text}, {"Column19", type text}, {"Column20", type text}, {"Column21", type text}, {"Column22", type text}, {"Column23", type text}, {"Column24", type text}, {"Column25", type text}, {"Column26", type text}, {"Column27", type text}, {"Column28", type text}, {"Column29", type text}}),

  #"Promoted headers" = Table.PromoteHeaders(#"Changed column type", [PromoteAllScalars = true]),

  #"Changed column type 1" = Table.TransformColumnTypes(#"Promoted headers", {{"Week 35.csv", type text}, {"Contract ID", type text}, {"Order Code", type text}, {"OrderCode2", type text}, {"Retailer", type text}, {"Retailer Ref", type text}, {"Surname", type text}, {"PostCode", type text}, {"Outbase", type text}, {"Visit Type", type text}, {"Visit Date", type text}, {"Arrival Time", type time}, {"Departure Time", type time}, {"Planned Time On Site", Int64.Type}, {"Date Entered", type text}, {"SKU", type text}, {"SKU Long Description", type text}, {"Debrief Code", Int64.Type}, {"Debrief Code Description", type text}, {"Route Number", Int64.Type}, {"Consignment Number", type text}, {"Stock With Customer", type text}, {"SKU Cartons", Int64.Type}, {"SKU Cube (Volume)", type number}, {"SKU Dwell Hierarchy", type text}, {"SKU Delivery Service Time", Int64.Type}, {"SKU Collection Service Time", Int64.Type}, {"SKU Install Service Time", Int64.Type}, {"Supplier Code", Int64.Type}, {"Supplier Description", type text}}),

  #"Changed column type with locale" = Table.TransformColumnTypes(#"Changed column type 1", {{"Visit Date", type date}}, "en-GB"),

  #"Changed column type with locale 1" = Table.TransformColumnTypes(#"Changed column type with locale", {{"Date Entered", type date}}, "en-GB"),

  #"Removed errors" = Table.RemoveRowsWithErrors(#"Changed column type with locale 1", {"Visit Date"}),

  #"Renamed columns 1" = Table.RenameColumns(#"Removed errors", {{"Week 35.csv", "Source"}}),

  #"Added custom" = Table.AddColumn(#"Renamed columns 1", "LookUp Ref", each [Order Code] & [PostCode] & Date.ToText([Visit Date],"dd/MM/yy")),

  #"Transform columns" = Table.TransformColumnTypes(#"Added custom", {{"LookUp Ref", type text}}),

  #"Replace errors" = Table.ReplaceErrorValues(#"Transform columns", {{"LookUp Ref", null}}),

  #"Filtered rows 1" = Table.SelectRows(#"Replace errors", each [Outbase] <> "Channel Islands"),

  #"Sorted rows" = Table.Sort(#"Filtered rows 1", {{"LookUp Ref", Order.Ascending}, {"Debrief Code Description", Order.Descending}}),

  #"Added index" = Table.AddIndexColumn(#"Sorted rows", "Index", 0, 1, Int64.Type),

  #"Added index 1" = Table.AddIndexColumn(#"Added index", "Index (2)", 1, 1, Int64.Type),

  #"Merged queries" = Table.NestedJoin(#"Added index 1", {"Index"}, #"Added index 1", {"Index (2)"}, "Added index 1", JoinKind.LeftOuter),

  #"Expanded Added index 1" = Table.ExpandTableColumn(#"Merged queries", "Added index 1", {"LookUp Ref"}, {"LookUp Ref.1"}),

  #"Renamed columns 2" = Table.RenameColumns(#"Expanded Added index 1", {{"LookUp Ref.1", "Prev Look Up"}}),

  #"Added custom 1" = Table.AddColumn(#"Renamed columns 2", "Count", each if [Prev Look Up] = [LookUp Ref] then 0 else 1),

  #"Removed columns" = Table.RemoveColumns(#"Added custom 1", {"Index", "Index (2)", "Prev Look Up"}),

  #"Transform columns 1" = Table.TransformColumnTypes(#"Removed columns", {{"Count", type text}}),

  #"Replace errors 1" = Table.ReplaceErrorValues(#"Transform columns 1", {{"Count", null}}),

  #"Added custom 2" = Table.AddColumn(#"Replace errors 1", "Client_Depot", each [Contract ID] & [Outbase]),

  #"Transform columns 2" = Table.TransformColumnTypes(#"Added custom 2", {{"Client_Depot", type text}}),

  #"Replace errors 2" = Table.ReplaceErrorValues(#"Transform columns 2", {{"Client_Depot", null}})

in

  #"Replace errors 2"

[lbendlin]

Try to avoid the nested join. Use the data model for joining or use Table.AddColumn.

There are a couple of steps you can eliminate. Refactor the code.

[lbendlin]

Also note that there is currently an issue in the Power BI service that may require a gateway for SharePoint Online sources even when they are not merged with other sources.

[Jebilaya]

Thanks for the reply @lbendlin   I don't follow the combine entries part you refer to but I have narrowed down the error to between the merged queries and expanded index steps.

It will evaluate up to and including the merged queries step but then the expanded added index introduces the error

[lbendlin]

You may want to refactor your queries and get rid of all the extra steps that were introduced when you chose to "combine entries" .  Usually you don't want to use that method.  Using Table.AddColumn is much cleaner and also a bit faster.