Dataflow: Error when working with SharePoint data source

[BIfanatic]

Hi everyone,

I am trying to connect to a relatively small Excel doc on SharePoint and ultimately create a dataflow. However, I get the following error code:

Error: We're sorry an error occurred during evaluation.

Since this does not tell me anything, I don’t know where to find the cause of this error - especially when PBI Desktop works fine with this code. I know about the differences in handling between dataflows and PBI Desktop, but it bumps me that this little piece of code does not work.

I started out with a small portion of my code, which should be easy to process. Check this out:

let

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

  Data = Source{[Name = "data.xlsx", #"Folder Path" = "https://company.sharepoint.com/sites/etcetera/"]}[Content],

  #"Imported Excel" = Excel.Workbook(Data, null, true),

  #"2016" = #"Imported Excel"{[Item = "2016", Kind = "Sheet"]}[Data],

  #"Promoted Headers" = Table.PromoteHeaders(#"2016", [PromoteAllScalars = true]),

  #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers", {{"Column 1", Int64.Type}, {" Column 2", Int64.Type}, {" Column 3", type date}, {" Column 4", Int64.Type}, {" Column 5", Int64.Type}, {" Column 6", type text}, {" Column 7", Int64.Type}, {" Column 8", type text}, {" Column 9", type text}, {" Column 10", type text}, {" Column 11", type text}, {" Column 12", Int64.Type}, {" Column 13", type text}, {" Column 14", type text}, {" Column 15", Int64.Type}, {" Column 16", type text}, {" Column 17", type datetime}, {" Column 18", type datetime}, {" Column 19", Int64.Type}, {" Column 20", Int64.Type}, {" Column 21", type text}, {" Column 22", type any}, {" Column 23", type any}, {"Column 24", Int64.Type}, {" Column 25", type number}, {" Column 26", Int64.Type}, {" Column 27 ", Int64.Type}, {" Column 28", type number}, {" Column  29", type number}}),

  #"Removed Other Columns" = Table.SelectColumns(#"Changed Type", {" Column 1", " Column 2", " Column 3", " Column 4", " Column 5", " Column 6", " Column 7", " Column 8", " Column 9", " Column 10", " Column 11", " Column 11", " Column 12", " Column 13", " Column 14", " Column 15"}),

  #"Renamed Columns" = Table.RenameColumns(#"Removed Other Columns", {{" Column 1", "Name 1"}, {" Column 2", " Name 2"}, {" Column 3", " Name 3"}, {" Column 4", " Name 4"}, {" Column 5", " Name 5"}}),

  #"Filtered Rows" = Table.SelectRows(#"Renamed Columns", each [Column1] <> null and [Column1] <> "")

in

  #"Filtered Rows"

Can anyone help me out here? Thanks in advance for your help.

[Anonymous]

Hi, 

I always get this error on my initial save, but then when I save again it goes away and the queries validate with no problem.

Will it let you save and refresh after you close the error?

~Kim

[BIfanatic · Accepted solution]

Hi Kimberley,

I was able to. I had contact with support and after a while they concluded that my workbook was too large. The problem is solved now.

[v-cherch-msft]

Hi @BIfanatic 

I cannot reproduce the same issue.You could try to open a support ticket. If you have a Pro account it is free. Go to https://support.powerbi.com. Scroll down and click "CREATE SUPPORT TICKET".

Regards,