cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
AgencyPowerBi Regular Visitor
Regular Visitor

PowerQuery import from Folder dont work for network folders

Hi,

 

this has been troubling me for a while now.

I can import from folder, if the folder is local.

But if it is on the network server, same domain, with permissions set to me as R/W, I cannot do nothing.

All I get is this message:

 

Unexpected error: Operation is not valid due to the current state of the object.
Details:
Microsoft.Mashup.Evaluator.Interface.ErrorException: Operation is not valid due to the current state of the object. ---> System.InvalidOperationException: Operation is not valid due to the current state of the object. ---> System.InvalidOperationException: Operation is not valid due to the current state of the object.
at Microsoft.Mashup.Engine1.Library.File.FileHelper.GetAccessControlEntries(AccessControlEntriesCache cache, String fullName, Boolean directory, FileSystemRights rights)
at Microsoft.Mashup.Engine1.Runtime.RecordValue.DemandRecordValue.get_Item(Int32 index)
at Microsoft.Mashup.Evaluator.Services.ValueSerializer.<>c__DisplayClass68_0.<WriteMetaField>b__0()
at Microsoft.Mashup.Evaluator.Services.ValueSerializer.HandleErrors(Action action, Action`1 handler)
at Microsoft.Mashup.Evaluator.Services.ValueSerializer.WriteMetaRecord(IRecordValue value, Boolean truncatedValue)
at Microsoft.Mashup.Evaluator.Services.ValueSerializer.WriteValueEnd(IValue value, IRecordValue meta, Boolean truncated)
at Microsoft.Mashup.Evaluator.Services.ValueSerializer.WriteLimitedValue(IValue value)
at Microsoft.Mashup.Evaluator.Services.ValueSerializer.<>c__DisplayClass108_0.<WritePreviewFieldValue>b__0()
at Microsoft.Mashup.Evaluator.Services.ValueSerializer.HandleErrors(Action action, Action`1 handler)
at Microsoft.Mashup.Evaluator.Services.ValueSerializer.<>c__DisplayClass99_2.<WritePreviewTable>b__2()
at Microsoft.Mashup.Evaluator.Services.ValueSerializer.HandleErrors(Action action, Action`1 handler)
at Microsoft.Mashup.Evaluator.Services.ValueSerializer.<>c__DisplayClass99_1.<WritePreviewTable>b__1()
at Microsoft.Mashup.Evaluator.Services.ValueSerializer.HandleErrors(Action action, Action`1 handler)
at Microsoft.Mashup.Evaluator.Services.ValueSerializer.HandleErrors(Action action, Action`1 handler)
at Microsoft.Mashup.Evaluator.Services.ValueSerializer.WritePreviewTable(IValue value)
at Microsoft.Mashup.Evaluator.Services.ValueSerializer.HandleErrors(Action action, Action`1 handler)
at Microsoft.Mashup.Evaluator.Services.ValueSerializer.WritePreviewValue(IValue value, Int32 serializeStructuredValueDepth)
at Microsoft.Mashup.Evaluator.Services.ValueSerializer.SerializePreviewValue(IEngine engine, IValue value, Action`2 rowCountCallback, Nullable`1 options)
at Microsoft.Mashup.Evaluator.SimpleDocumentEvaluator.ValuePreviewValueSource.TableValuePreviewValueSource.SerializeRows(Int32 count)
at Microsoft.Mashup.Evaluator.SimpleDocumentEvaluator.ValuePreviewValueSource.TableValuePreviewValueSource.get_SmallValue()
at Microsoft.Mashup.Evaluator.Interface.TracingPreviewValueSource.get_SmallValue()
at Microsoft.Mashup.Evaluator.RemotePreviewValueSource.<>c__DisplayClass0_0.<RunStub>b__0()
at Microsoft.Mashup.Evaluator.EvaluationHost.ReportExceptions(IHostTrace trace, IEngineHost engineHost, IMessageChannel channel, Action action)
--- End of inner exception stack trace ---
at Microsoft.Mashup.Evaluator.EvaluationHost.<>c__DisplayClass11_0.<TryReportException>b__1()
at Microsoft.Mashup.Common.SafeExceptions.IgnoreSafeExceptions(IEngineHost host, IHostTrace trace, Action action)
at Microsoft.Mashup.Evaluator.EvaluationHost.TryReportException(IHostTrace trace, IEngineHost engineHost, IMessageChannel channel, Exception exception)
at Microsoft.Mashup.Evaluator.EvaluationHost.ReportExceptions(IHostTrace trace, IEngineHost engineHost, IMessageChannel channel, Action action)
at Microsoft.Mashup.Evaluator.RemotePreviewValueSource.RunStub(IEngineHost engineHost, IMessageChannel channel, Func`1 getPreviewValueSource)
at Microsoft.Mashup.Evaluator.RemoteDocumentEvaluator.Service.<>c__DisplayClass12_1`1.<OnBeginGetResult>b__0()
at Microsoft.Mashup.Evaluator.EvaluationHost.ReportExceptions(IHostTrace trace, IEngineHost engineHost, IMessageChannel channel, Action action)
at Microsoft.Mashup.Evaluator.RemoteDocumentEvaluator.Service.OnBeginGetResult[T](IMessageChannel channel, BeginGetResultMessage message, Action`1 action)
at Microsoft.Mashup.Evaluator.RemoteDocumentEvaluator.Service.OnBeginGetPreviewValueSource(IMessageChannel channel, BeginGetPreviewValueSourceMessage message)
at Microsoft.Mashup.Evaluator.MessageHandlers.TryDispatch(IMessageChannel channel, Message message)
at Microsoft.Mashup.Evaluator.ChannelMessenger.ChannelMessageHandlers.TryDispatch(IMessageChannel channel, Message message)
at Microsoft.Mashup.Evaluator.MessageHandlers.Dispatch(IMessageChannel channel, Message message)
at Microsoft.Mashup.Evaluator.ChannelMessenger.OnMessageWithUnknownChannel(IMessageChannel baseChannel, MessageWithUnknownChannel messageWithUnknownChannel)
at Microsoft.Mashup.Evaluator.MessageHandlers.TryDispatch(IMessageChannel channel, Message message)
at Microsoft.Mashup.Evaluator.ChannelMessenger.ChannelMessageHandlers.TryDispatch(IMessageChannel channel, Message message)
at Microsoft.Mashup.Evaluator.MessageHandlers.Dispatch(IMessageChannel channel, Message message)
at Microsoft.Mashup.Evaluator.EvaluationHost.Run()
at Microsoft.Mashup.Container.EvaluationContainerMain.Run(Object args)
at Microsoft.Mashup.Evaluator.SafeThread2.<>c__DisplayClass9_0.<CreateAction>b__0(Object o)
at Microsoft.Mashup.Container.EvaluationContainerMain.SafeRun(String[] args)
at Microsoft.Mashup.Container.EvaluationContainerMain.Main(String[] args)
--- End of inner exception stack trace ---
at Microsoft.Mashup.Evaluator.EvaluationHost.OnException(IEngineHost engineHost, IMessageChannel channel, ExceptionMessage message)
at Microsoft.Mashup.Evaluator.MessageHandlers.TryDispatch(IMessageChannel channel, Message message)
at Microsoft.Mashup.Evaluator.MessageHandlers.Dispatch(IMessageChannel channel, Message message)
at Microsoft.Mashup.Evaluator.ChannelMessenger.ChannelMessageHandlers.TryDispatch(IMessageChannel channel, Message message)
at Microsoft.Mashup.Evaluator.MessageHandlers.Dispatch(IMessageChannel channel, Message message)
at Microsoft.Mashup.Evaluator.Interface.IMessageChannelExtensions.WaitFor[T](IMessageChannel channel)
at Microsoft.Mashup.Evaluator.RemotePreviewValueSource.PreviewValueSource.WaitFor(Func`1 condition, Boolean disposing)
at Microsoft.Mashup.Evaluator.RemotePreviewValueSource.PreviewValueSource.get_SmallValue()
at Microsoft.Mashup.Evaluator.Interface.TracingPreviewValueSource.get_SmallValue()
at Microsoft.Mashup.Host.Document.Analysis.PackageDocumentAnalysisInfo.PackagePartitionAnalysisInfo.SetPreviewValue(EvaluationResult2`1 result, Func`1 getStaleSince, Func`1 getSampled)

 

Now for the folder that is given permissions to everyone, it works, but I cannot make my network admins do this for the all foders.

So, what I must change on the folder, for this to work?

8 REPLIES 8
Stachu Super Contributor
Super Contributor

Re: PowerQuery import from Folder dont work for network folders

I used network folders a lot, never had an issue as long as they had a path like local folder, e.g.
Z:\NetworkFolder\

is that the case?
or is the format more like:
Domain//NetworkDrive/NetworkFolder/
you could then try with Web or SharePoint folder connectors, but I always had to change it more 'local' version of the path - can you map this drive to your pc?

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

AgencyPowerBi Regular Visitor
Regular Visitor

Re: PowerQuery import from Folder dont work for network folders

Well if I map it as a local drive, it doesn't help also.

 

I have one network share that works, but generally they do not.

One share that works is totaly open to everyone.. while others have network policies for different users.. Im just wondering what is actually making the problem, because I can link to specific excel on those shares and everything works.

Its just with folder import, its making problems 😕

 

so, its not how you reference it.. thanks for the tip, though

 

Stachu Super Contributor
Super Contributor

Re: PowerQuery import from Folder dont work for network folders

Hmm, I am not sure I understand correctly - you can link to a folder with "/" as a separator and it works? To my knowledge Folder.Files only takes paths with "\" reference.

Connecting to a single file is a different because the file just have to be there, it's like connecting to csv on Web - no issues at all
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

AgencyPowerBi Regular Visitor
Regular Visitor

Re: PowerQuery import from Folder dont work for network folders

\\server\shared folder\folder

 

it works for just one of the shared folders.

So if I address one excel through the same address, it works..if I address folder, I get the code I put in the first message.

 

 

 

Stachu Super Contributor
Super Contributor

Re: PowerQuery import from Folder dont work for network folders


@AgencyPowerBi wrote:

\\server\shared folder\folder

 

it works for just one of the shared folders.

So if I address one excel through the same address, it works..if I address folder, I get the code I put in the first message.


so iy works if you connect to a specific file in that folder, not to the folder itself, correct? 

the reason it works for a file is the code that connects to a file uses File.Contents (or possibly Web.Contents), that can handle both "/" and "\"

let
    Source = Excel.Workbook(File.Contents("C:\test\file.xlsx"), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data]
in
    Sheet1_Sheet

when you connect to the folder this gets generated:

let
    Source = Folder.Files("C:\test\")
in
    Source

Folder.Files only handles "\" - at least from my experience. You can also try Folder.Contents, but my guess is the result will be the same. If you map the network drive to a local path it should work without problems

 

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

AgencyPowerBi Regular Visitor
Regular Visitor

Re: PowerQuery import from Folder dont work for network folders

well, this is interesting, because it actually works 🙂 

I'll do some more testing, but yeah..

 

Thing is, if I want to make a data silo for my company, each computer maps network shares differently, and it makes a problem.

That is why I tend to use \\server\share structure.

 

The thing is.. for one share that works my path is:

 

= Folder.Files(Source&"\source\TV")

where Source is defined in seperate query as "\\server\share_folder\folder"

and it works for only specific share_folder\folder

 

on all other shares it doesn't work, so it must be something other than folder.files doesn't recognize "\"

I would like to know what it is, because other solution makes me organizational problems..

 

Thanks for advice so far, it's been really useful

 

Stachu Super Contributor
Super Contributor

Re: PowerQuery import from Folder dont work for network folders

from my experience "\" is not an issue, "/" never worked for me, but it doesn't seem to be relevant in your case


so you can use Files.Folder for a single folder on a specific server, but not for any other folders on the same server? Interesting indeed, anything special about that folder? connecting to subfolders or higher folders works as well?

 

I agree mapping is not a sustainable solution, but if it works for you, then it proves it's not an access issue

 

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

AgencyPowerBi Regular Visitor
Regular Visitor

Re: PowerQuery import from Folder dont work for network folders

I find nothing special about it except it has no share policies.. its public to all within domain.

That is why Im asking around 🙂 not sure what could block access to folder.

 

Helpful resources

Announcements
New Kudos Received Badges Coming

New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (1,667)