Postgre Database Apply Query Changes Error

Hi Qiuyun,

1. I used built-in PostgreSQL database connector to connect a local postgre database

2. I am currently running 64 bit July release PBI desktop

3. I can preview the table without any issue. However, once I clicked on "Load" in data navigator screen or (if I chose edit in data navigator) then when hit  "Close &Apply" , it will give me an error specified cast is not valid. I tried tables with data and without, it did not make any difference 

4. I tried to create a new database in postgre. Created one test table with one test column as numeric and one row of data. Failed

Feedback Type:
Frown (Error)

Timestamp:
2017-07-26T13:41:39.4123673Z

Local Time:
2017-07-26T09:41:39.4123673-04:00

Product Version:
2.48.4792.721 (PBIDesktop) (x64)

Release:
July 2017

IE Version:
11.0.9600.18698

OS Version:
Microsoft Windows NT 6.3.9600.0 (x64 en-US)

CLR Version:
4.6.1 or later [Release Number = 394271]

Workbook Package Info:
1* - en-US, Query Groups: 0, fastCombine: Disabled, runBackgroundAnalysis: True.

Peak Working Set:
444 MB

Private Memory:
286 MB

Peak Virtual Memory:
33.6 GB

Error Message:
Specified cast is not valid.

Stack Trace:
Microsoft.Mashup.Evaluator.Interface.ErrorException: Specified cast is not valid. ---> Microsoft.Mashup.Evaluator.Interface.ErrorException: Specified cast is not valid. ---> Microsoft.Mashup.Evaluator.Interface.ErrorException: Specified cast is not valid. ---> System.InvalidCastException: Specified cast is not valid. ---> System.InvalidCastException: Specified cast is not valid.
at Microsoft.OleDb.Serialization.PageWriter.WriteSchema(DataTable schemaTable)
at Microsoft.Mashup.Evaluator.RemotePageReader.<>c__DisplayClass7.<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__DisplayClass7.<TryReportException>b__6()
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.RemotePageReader.RunStub(IEngineHost engineHost, IMessageChannel channel, Func`1 getPageReader)
at Microsoft.Mashup.Evaluator.RemoteDocumentEvaluator.Service.<>c__DisplayClass1e`1.<OnBeginGetResult>b__1b()
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.OnBeginGetDataReaderSource(IMessageChannel channel, BeginGetDataReaderSourceMessage 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.Evaluator.SafeThread2.<>c__DisplayClass15.<CreateAction>b__14(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.MessageBasedInputStream.ReadNextChunkAndCheckIfClosed()
at Microsoft.Mashup.Evaluator.MessageBasedInputStream.ReadNextChunk()
at Microsoft.Mashup.Evaluator.ChunkedInputStream.Read(Byte[] buffer, Int32 offset, Int32 count)
at System.IO.BinaryReader.FillBuffer(Int32 numBytes)
at System.IO.BinaryReader.ReadInt32()
at Microsoft.Mashup.Evaluator.ITableSourceSerializationExtensions.ReadITableSource(BinaryReader reader)
at Microsoft.Mashup.Evaluator.RemotePageReader.CreateProxy(IEngineHost engineHost, IMessageChannel channel, ExceptionHandler exceptionHandler)
at Microsoft.Mashup.Evaluator.RemoteDocumentEvaluator.DataReaderSourceRemoteEvaluation.GetResult(Boolean enableFirewall)
at Microsoft.Mashup.Evaluator.RemoteDocumentEvaluator.RemoteEvaluation.Evaluate(Boolean enableFirewall)
--- End of inner exception stack trace ---
at Microsoft.Mashup.Evaluator.RemoteDocumentEvaluator.RemoteEvaluation`1.TryCompleteWithException(Exception exception)
at Microsoft.Mashup.Evaluator.RemoteDocumentEvaluator.RemoteEvaluation.Evaluate(Boolean enableFirewall)
at Microsoft.Mashup.Evaluator.EvaluatorThreadPool.EvaluatorThread(Object state)
at Microsoft.Mashup.Evaluator.SafeThread2.<>c__DisplayClass15.<CreateAction>b__14(Object o)
at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
at System.Threading.ThreadHelper.ThreadStart(Object obj)
--- End of inner exception stack trace ---

Server stack trace:

Exception rethrown at [0]:
at Microsoft.OleDb.AsyncResult`1.get_Value()
at Microsoft.Mashup.OleDbProvider.DataHost.NonBufferingMashupEvaluator.MashupEvaluation.OnEvaluationComplete(AsyncResult`1 result)

Exception rethrown at [1]:
at Microsoft.OleDb.AsyncResult`1.get_Value()
at Microsoft.Mashup.OleDbProvider.MashupRowset.get__Rowset()
at Microsoft.OleDb.Rowset.Microsoft.OleDb.IRowset.AddRefRows(DBCOUNTITEM cRows, HROW* nativeRows, UInt32* nativeRefCounts, DBROWSTATUS* nativeRowStatus)
at Microsoft.Mashup.OleDbProvider.TracingRowset.Microsoft.OleDb.IRowset.AddRefRows(DBCOUNTITEM cRows, HROW* nativeRows, UInt32* nativeRefCounts, DBROWSTATUS* nativeRowStatus)

Invocation Stack Trace:
at Microsoft.Mashup.Host.Document.ExceptionExtensions.GetCurrentInvocationStackTrace()
at Microsoft.Mashup.Client.ClientShared.StackTraceInfo..ctor(String exceptionStackTrace, String invocationStackTrace)
at Microsoft.Mashup.Client.ClientShared.FeedbackErrorInfo..ctor(String message, Nullable`1 errorCode, String requestId, Exception exception)
at Microsoft.Mashup.Client.ClientShared.UnexpectedExceptionHandler.<>c__DisplayClass1.<HandleException>b__0()
at Microsoft.Mashup.Host.Document.SynchronizationContextExtensions.<>c__DisplayClass3.<SendAndMarshalExceptions>b__0(Object null)
at System.RuntimeMethodHandle.InvokeMethod(Object target, Object[] arguments, Signature sig, Boolean constructor)
at System.Reflection.RuntimeMethodInfo.UnsafeInvokeInternal(Object obj, Object[] parameters, Object[] arguments)
at System.Delegate.DynamicInvokeImpl(Object[] args)
at System.Windows.Forms.Control.InvokeMarshaledCallbackDo(ThreadMethodEntry tme)
at System.Windows.Forms.Control.InvokeMarshaledCallbackHelper(Object obj)
at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
at System.Windows.Forms.Control.InvokeMarshaledCallback(ThreadMethodEntry tme)
at System.Windows.Forms.Control.InvokeMarshaledCallbacks()
at System.Windows.Forms.Control.WndProc(Message& m)
at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(IntPtr dwComponentID, Int32 reason, Int32 pvLoopData)
at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)
at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
at System.Windows.Forms.Form.ShowDialog(IWin32Window owner)
at Microsoft.Mashup.Client.ClientShared.WindowManager.ShowDialog[T](T form, IWin32Window owner)
at Microsoft.Mashup.Client.ClientShared.Ux.FloatingDialog.FloatingDialog.ShowDialogWithTimeout(IWin32Window owner, Nullable`1 showTimeout)
at Microsoft.PowerBI.Client.Windows.FloatingDialog.KoLoadToReportDialog.StartEvaluationAndShowDialog(IEnumerable`1 queriesToLoad, Nullable`1 showTimeout, Boolean requireFullDataRefresh)
at Microsoft.PowerBI.Client.Windows.FloatingDialog.KoLoadToReportDialog.TryShowDialogForQueries(Report report, IWin32Window ownerWindow, WindowsHost windowsHost, IQueryServices queryServices, IEnumerable`1 queriesToLoad, LocalizedString title, Boolean requireFullDataRefresh)
at Microsoft.PowerBI.Client.Windows.PowerBIDataImporter.LoadQueries(IEnumerable`1 queriesToLoad)
at Microsoft.PowerBI.Client.Windows.PowerBIDataImporter.OnImportSuccess(IEnumerable`1 addedQueries, DataImportDestination destination)
at Microsoft.Mashup.Client.ClientShared.DataImporter.ProcessImportedQueries(IEnumerable`1 importedQueries, DataImportDestination destination)
at Microsoft.Mashup.Client.ClientShared.DataImporter.ImportNavigationSource(Query query)
at Microsoft.Mashup.Client.ClientShared.DataImporter.OnGetPreviewResult(PreviewResult preview, Query query, String sourceID, String formulaTitle, Nullable`1 explicitImportDestination, Boolean isNewQuery, Boolean isFromEditor)
at Microsoft.Mashup.Client.ClientShared.DataImporter.<>c__DisplayClass1b.<OnQuerySettingsResolved>b__18()
at Microsoft.Mashup.Host.Document.ExceptionHandlerExtensions.HandleExceptions(IExceptionHandler exceptionHandler, Action action)
at System.RuntimeMethodHandle.InvokeMethod(Object target, Object[] arguments, Signature sig, Boolean constructor)
at System.Reflection.RuntimeMethodInfo.UnsafeInvokeInternal(Object obj, Object[] parameters, Object[] arguments)
at System.Delegate.DynamicInvokeImpl(Object[] args)
at System.Windows.Forms.Control.InvokeMarshaledCallbackDo(ThreadMethodEntry tme)
at System.Windows.Forms.Control.InvokeMarshaledCallbackHelper(Object obj)
at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
at System.Windows.Forms.Control.InvokeMarshaledCallback(ThreadMethodEntry tme)
at System.Windows.Forms.Control.InvokeMarshaledCallbacks()
at System.Windows.Forms.Control.WndProc(Message& m)
at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(IntPtr dwComponentID, Int32 reason, Int32 pvLoopData)
at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)
at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
at System.Windows.Forms.Form.ShowDialog(IWin32Window owner)
at Microsoft.Mashup.Client.ClientShared.WindowManager.ShowDialog[T](T form, IWin32Window owner)
at Microsoft.PowerBI.Client.Program.<>c__DisplayClassd.<Main>b__0()
at Microsoft.Mashup.Host.Document.ExceptionHandlerExtensions.HandleExceptions(IExceptionHandler exceptionHandler, Action action)
at Microsoft.PowerBI.Client.Program.Main(String[] args)

User ID:
ed841862-003c-462c-b79d-816d389e9131

Session ID:
7827b53a-53fd-4e38-bb7a-a6e8e2c1e930

Telemetry Enabled:
True

Model Default Mode:
Import

Snapshot Trace Logs:
C:\Users\xliu\AppData\Local\Microsoft\Power BI Desktop\FrownSnapShot553622787.zip

Performance Trace Logs:
C:\Users\xliu\AppData\Local\Microsoft\Power BI Desktop\PerformanceTraces.zip

Disabled Preview Features:
PBI_shapeMapVisualEnabled
PBI_EnableReportTheme
PBI_numericSlicerEnabled
PBI_SpanishLinguisticsEnabled
PBI_PbiServiceLiveConnect
PBI_daxTemplatesEnabled
CustomConnectors

Enabled DirectQuery Options:
PBI_DirectQuery_Unrestricted

Cloud:
GlobalCloud

DPI Scale:
100%

Supported Services:
Power BI

Formulas:

section Section1;

shared #"public userpickerfilterrole" = let
Source = PostgreSQL.Database("jira", "jiradb"),
public_userpickerfilterrole = Source{[Schema="public",Item="userpickerfilterrole"]}[Data]
in
public_userpickerfilterrole;

Power BI's lack of TESTED, WORKING MICROSOFT PROVIDED software to connect to PostgreSQL is PATHETIC.

PostgreSQL is the FOURTH most popular DBMS on the planet. There is NO EXCUSE for this pathetic "support" from Microsoft.

I say that as a database industry veteran of 3 decades working for database vendors, media companies, large companies' IT organizations and startups. I can't believe how crappy this is just to get a hello world level Power BI visualization using PostgreSQL. 

SSRS can do it. Tableau can do it. But Microsoft's wunderkind is spending too much time on nonsense like inverted pyramids rather than basic functionality to significant customer bases.

Time to escalate this to sr mgmt because I'm sure they have no idea what their underlings are messing up.