Power Query in Excel different from Power Query in Power BI

Question

Hello everyone! I mainly use Power Query in Power BI and recently in Excel. I noticed some differences between these two "versions" of Power Query (in the M language ,for instance). Recently, I noticed a blocking difference: in PQry for PBI I can access all the files of a SharePoint site library. If I do the same operation with PQry in Excel, some folders are invisible and inaccessible. Have you ever had this experience? Do you know the cause and solution to this weirdness? Thanks for your help!

Answer 1

You should not post the same question on 2 different forums. It is disrespectful to those who answer questions, only to find out that you have already got the answer somewhere else. 

Answer 2

Hi @Christian CROCHE ,
Did you need any permissions to access these files in SharePoint?
Did all kinds of files cannot be load in Excel?
I suggest you read this article and confirm if it is helpful:
https://learn.microsoft.com/en-us/power-query/connectors/sharepointfolder
Please be a bit more precise to explain your problem so that I can get more accurate solutions to this problem. I’m glad to help and follow up your reply.

---

If the response is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 3

Dear @Emi Zhang-MSFT , I have the owner permissions on the SharePoint website I’m pointing at. All types of file are visible to me and I can access them in the SharePoint. I want to access a specific file in a given folder and select the last version of it by its "last modified date" property. This is the process that I follow (the screenshots are in French but you’ll certainly be able to see what it’ about). I do this at the same time in Power Query for Excel and in Power Query for Power BI:

• I choose a connexion to a SharePoint folder:
  
• I then navigate to this folder:
  
• I connect to the site with my professional credentials and select the upmost level to apply the settings
  
• I’m then presented with the list of files (at least, a part of them):
  
• I choose to modify this list in order to select the ONE file I’m interested in:
  I filter the list by folder starting with…:
  
• I then get an empty list in Pwr Qry for Excel whereas I get a correct list of the files I need in Pwr Qry for PBI:
  In excel:
  In PBI:

Is there any explanation to this behavior? I noticed that the behavior has slightly changed from the previous time I did it: in Excel, I could see some sub-folders of the folder "Médical". Today, I can’t see them anymore.

Thanks for your help!

Answer 4

I may have found something indicating why I can’t find the same thing in Excel. In the Query options / Account, I can’t connect to my account and get this error:

I don’t understand what this means but it may be a track to find the solution!
The details of the error shows:
Feedback Type:
Frown (Error)

Error Message:
Nous ne pouvons pas contacter le service. Cela peut être dû à votre connexion réseau, ou le service n'est peut-être pas disponible. Si le problème persiste, contactez votre administrateur.

Stack Trace:
à System.Environment.GetStackTrace(Exception e, Boolean needFileInfo)
à System.Environment.get_StackTrace()
à Microsoft.Mashup.Client.UI.Shared.StackTraceInfo.GetCurrent()
à Microsoft.Mashup.Client.UI.Shared.HtmlControls.Options.AccountPaneHtmlControl.OnClientConfigPollerTimeout(Object sender, EventArgs e)
à Microsoft.Mashup.Client.UI.Shared.ClientConfigPoller.<>c__DisplayClass2.<OnTimeoutWrapper>b__0()
à Microsoft.Mashup.Host.Document.ExceptionHandlerExtensions.HandleExceptions(IExceptionHandler exceptionHandler, Action action)
à Microsoft.Mashup.Client.UI.Shared.ClientConfigPoller.OnTimeoutWrapper(Object sender, EventArgs e)
à System.Windows.Forms.Timer.OnTick(EventArgs e)
à System.Windows.Forms.Timer.TimerNativeWindow.WndProc(Message& m)
à System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
à System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
à System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
à System.Windows.Forms.Application.ThreadContext.LocalModalMessageLoop(Form form)
à System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)
à System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
à System.Windows.Forms.Form.ShowDialog(IWin32Window owner)
à Microsoft.Mashup.Client.UI.Shared.WebDialogs.WebDialog.<>n__FabricatedMethod8(IWindowHandle )
à Microsoft.Mashup.Client.UI.Shared.WindowManager.ShowModalT
à Microsoft.Mashup.Client.Excel.FloatingDialogs.OptionsDialog.Show(IWindowHandle owner, IUIHost uiHost, ApplicationTracingHost applicationTracingHost, Queries queries, OptionsDialogInfo dialogInfo, Boolean applicationAllowsUpdate, Boolean applicationSupportsDataModel, Boolean isDataCatalogEnabledByFederationEnv, Boolean canEditFile)
à Microsoft.Mashup.Client.Excel.DialogManager.ShowSettingsDialog(IWindowHandle ownerWindow, IWorkbook workbook)
à Microsoft.Mashup.Client.Excel.Shim.IWindowContextExtensions.TryInvokeOnParentWorkbook(IWindowContext windowContext, Action`1 action)
à Microsoft.Mashup.Client.Excel.ExcelRibbonHandler.OnOptionsAction(IWindowContext windowContext)
à Microsoft.Mashup.Client.Excel.NativeRibbonHandler.<>c__DisplayClass1.<OnRibbonButtonAction>b__0()
à Microsoft.Mashup.Host.Document.ExceptionHandlerExtensions.HandleExceptions(IExceptionHandler exceptionHandler, Action action)
à Microsoft.Mashup.Client.Excel.ExcelCallbackManager.InvokeAndReturnHResult(Action action)

Invocation Stack Trace:
à Microsoft.Mashup.Host.Document.ExceptionExtensions.GetCurrentInvocationStackTrace()
à Microsoft.Mashup.Client.UI.Shared.StackTraceInfo.GetCurrent()
à Microsoft.Mashup.Client.UI.Shared.HtmlControls.Options.AccountPaneHtmlControl.OnClientConfigPollerTimeout(Object sender, EventArgs e)
à Microsoft.Mashup.Client.UI.Shared.ClientConfigPoller.<>c__DisplayClass2.<OnTimeoutWrapper>b__0()
à Microsoft.Mashup.Host.Document.ExceptionHandlerExtensions.HandleExceptions(IExceptionHandler exceptionHandler, Action action)
à Microsoft.Mashup.Client.UI.Shared.ClientConfigPoller.OnTimeoutWrapper(Object sender, EventArgs e)
à System.Windows.Forms.Timer.OnTick(EventArgs e)
à System.Windows.Forms.Timer.TimerNativeWindow.WndProc(Message& m)
à System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
à System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
à System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
à System.Windows.Forms.Application.ThreadContext.LocalModalMessageLoop(Form form)
à System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)
à System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
à System.Windows.Forms.Form.ShowDialog(IWin32Window owner)
à Microsoft.Mashup.Client.UI.Shared.WebDialogs.WebDialog.<>n__FabricatedMethod8(IWindowHandle )
à Microsoft.Mashup.Client.UI.Shared.WindowManager.ShowModalT
à Microsoft.Mashup.Client.Excel.FloatingDialogs.OptionsDialog.Show(IWindowHandle owner, IUIHost uiHost, ApplicationTracingHost applicationTracingHost, Queries queries, OptionsDialogInfo dialogInfo, Boolean applicationAllowsUpdate, Boolean applicationSupportsDataModel, Boolean isDataCatalogEnabledByFederationEnv, Boolean canEditFile)
à Microsoft.Mashup.Client.Excel.DialogManager.ShowSettingsDialog(IWindowHandle ownerWindow, IWorkbook workbook)
à Microsoft.Mashup.Client.Excel.Shim.IWindowContextExtensions.TryInvokeOnParentWorkbook(IWindowContext windowContext, Action`1 action)
à Microsoft.Mashup.Client.Excel.ExcelRibbonHandler.OnOptionsAction(IWindowContext windowContext)
à Microsoft.Mashup.Client.Excel.NativeRibbonHandler.<>c__DisplayClass1.<OnRibbonButtonAction>b__0()
à Microsoft.Mashup.Host.Document.ExceptionHandlerExtensions.HandleExceptions(IExceptionHandler exceptionHandler, Action action)
à Microsoft.Mashup.Client.Excel.ExcelCallbackManager.InvokeAndReturnHResult(Action action)

Supports Premium Content:
True