cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Accokeekian
Frequent Visitor

"Unexpected error" when reading sharepoint folder full of large text files using Dataflows

Hi there,

 

Excited for the new features of dataflows from the Nov 2019 update which allows the same "combine" files experience as in power query on desktop. Unfourtunately, when I attempt to load a folder of delimited text files (.log), I recieve the generic error of "Unexpected Error" and a session ID when I attempt to execute the "combine files" command. 

 

I get this same error when accessing the folder using the sharepoint online connector as well as a local folder connection using my gateway.

 

Has anyone else encountered this issue?

Capture.JPG

13 REPLIES 13
fabo
Advocate II
Advocate II

Hi everyone.

I'm facing the same issue here.  I'm trying to combine Excel files from a Sharepoint folder, but when I try to expand the content I get the "unexpected error" message.  Has this been solved?  Is there a workaround?

0.1.PNG

 

 

 

 

 

 

 

1.PNG

 

 

 

 

 

 

2.PNG

 

 

 

 

 

 

 

 

 

 

 

 

 

Greetings!

@fabo 

Accokeekian
Frequent Visitor

No resolution just yet. I have a support ticket in and we're working through the issue.

Initially msft concluded that it was a file type issues but after testing that was deemed to not be the case. Your issue with Excel files contributes to that view.

I'll link your comment to my case and keep you updated.

Thank you for replying @Accokeekian .

 

My Excel files have .xlsm extension so it doesn't seem to be the file type.  I leave you the mashup text, it might be useful somehow (translated from spanish):

 

 

section Section1;
shared data_oferta_prog = let
  Source = SharePoint.Files("https://xxxxxxxxxxx.sharepoint.com/sites/xxxxxxxxxxx", [ApiVersion = 15]),
  #"Filtered rows" = Table.SelectRows(Source, each ([Extension] = ".xlsm")),
  #"Keep content" = Table.SelectColumns(#"Filtered rows", {"Content"}),
  #"Filtered hidden files" = Table.SelectRows(#"Keep content", each [Attributes]?[Hidden]? <> true)
in
  #"Filtered hidden files";
shared #"Example file" = let
  Source = SharePoint.Files("https://xxxxxxxxxxx.sharepoint.com/sites/xxxxxxxxxxx", [ApiVersion = 15]),
  #"Filtered rows" = Table.SelectRows(Source , each ([Extension] = ".xlsm")),
  #"Keep content" = Table.SelectColumns(#"Filtered rows", {"Content"}),
  #"Filtered hidden files" = Table.SelectRows(#"Keep content", each [Attributes]?[Hidden]? <> true),
  Navegation = #"Filtered hidden files"{0}[Content]
in
  Navegation;
shared Parameter = let
  Parameter = #"Example file" meta [IsParameterQuery = true, IsParameterQueryRequired = true, Type = type binary]
in
  Parameter;

 

 

Thanks!

jmdh
Advocate III
Advocate III

Hope this helps: I have faced the same issue many times.
Now i prefer to use SharePoint.Contents instead of SharePoint.Files which is both easier to reach the files i want ie (their folder) and seems to work well.

This unexpected error has started in may or June i believe and no one seems to have acknoledged it.

 

I would love an extension to the API allowing to directly access a given folder.

Hi @jmdh , thank you for your suggestion.

Unfortunately, it didn't workout for me.  I can summarize the steps for both methods as followed:

Using SharePoint.Files

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

2. #"Keep Content" = Table.SelectColumns(Source ,{"Content"})

Using SharePoint.Contents

1. Source = SharePoint.Contents("https://xxxxx.sharepoint.com/sites/xxxxx", [ApiVersion = 15])

2. #"Shared Documents" = Source{[Name="Shared Documents"]}[Content]
3. Data = #"Shared Documents"{[Name="Data"]}[Content]

4. #"Keep Content" = Table.SelectColumns(Data ,{"Content"})

With both approaches I come to the same next step: combine files (expand the content), selecting the two down arrows next to the field name Content.

0.1.PNG

That's the point where I get this unexpected error, no matter which of the previous functions I use.

 

jmdh
Advocate III
Advocate III

May sound dumb but does it work on PowerBI Desktop ? 

Yes, it does work on PowerBI Desktop.  No problem there.

I was trying to replicate the same over dataflows.

jmdh
Advocate III
Advocate III

I will look further tonight : however the same code does not yield the same results in Desktop and Dataflow.
In short: Desktop creates a Content column which contains the Binary of each excel file, while Dataflow creates a Content column which contains a Table value for each file...

v-shex-msft
Community Support
Community Support

Hi @Accokeekian ,

Any advanced operations you add in your query tables? If these operations work on the desktop but not able to execute on dataflow side, it means some of the custom steps are invalid on the dataflow side.

You can try to assign these queries to computed entities to confirm if they work in high permissions. (computed entity allow more operations)

Using computed entities on Power BI Premium 

BTW, if you transform your '.log' file to '.txt', did these queries work on dataflow side? (for security reasons, some of the functions only work with common file types when you work on service side)
Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Thanks for your response. At this point in testing I am not using any complex operations built into my queries, I am just using the basic functionality of combining a series of files within a sharepoint folder using the UI.

 

I had read that filetypes could be an issue and have experiemted with renaming the files from .log to .txt, .csv and other more common types with no sucess.

 

FYI - This is all taking place in a premium workspace

Hi @Accokeekian ,

I can't find out the reason of your scenario from your description. Maybe you can open a support ticket to get further support from power bi team.

submit a support ticketsubmit a support ticket

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
jmdh
Advocate III
Advocate III

Hi is suspect this has to do with the fact that basically you can't use all the new commands in dataflow that require a Premium licence. 

venal
Memorable Member
Memorable Member

@Accokeekian 

Please refer the support page for known issue related to Data Flow.

 

If the issue is different, please submit a ticket by clicking on "Create Support Ticket".

 

If this post helps, then please consider Accept it as the solution to help the other members find it more

If this post was helpful may I ask you to mark it as solution and give it some kudos?

 

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors
Top Kudoed Authors