SOLVED

Pivot table problem obtaining data

Copper Contributor

Hi,

 

I'm creating pivot table from multiple tables using queries connection. But suddenly it have some error and my pivot table have problem obtaining data when I refresh it. Here is the error details:

 

Feedback Type:
Frown (Error)

Error Message:
Container exited unexpectedly with code 0xFFFFFFFF. PID: 12464.
Used features: (none).

Stack Trace:

Server stack trace:
at Microsoft.Mashup.Evaluator.ErrorTranslatingMessenger.MessageChannel.Read()
at Microsoft.Mashup.Evaluator.ChannelMessenger.Read(MessageChannel channel)
at Microsoft.Mashup.Evaluator.ChannelMessenger.MessageChannel.Read()
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.Common.ChunkedInputStream.Read(Byte[] buffer, Int32 offset, Int32 count)
at Microsoft.OleDb.Serialization.PageReader.Read(Byte[] buffer, Int32 offset, Int32 count)
at Microsoft.OleDb.Serialization.PageReader.ReadString()
at Microsoft.OleDb.Serialization.PageReader.ReadException()
at Microsoft.OleDb.Serialization.PageReader.ReadExceptionRow()
at Microsoft.OleDb.Serialization.PageReader.ReadExceptionRows(Dictionary`2 exceptionRows)
at Microsoft.OleDb.Serialization.ColumnsPage.Deserialize(PageReader reader)
at Microsoft.OleDb.Serialization.OleDbPageReader.Read(ColumnsPage page)
at Microsoft.Mashup.Engine.Interface.Tracing.TracingPageReader.Read(IPage page)
at Microsoft.Mashup.OleDbProvider.DataHost.BufferingMashupEvaluator.BufferingMashupEvaluation.SerializeResult(IDataReaderSource dataReaderSource, Stream stream)
at Microsoft.Mashup.OleDbProvider.DataHost.BufferingMashupEvaluator.BufferingMashupEvaluation.GetBufferedReaderSource(AsyncResult`1 result)
at Microsoft.Mashup.OleDbProvider.DataHost.BufferingMashupEvaluator.BufferingMashupEvaluation.OnEvaluationComplete(AsyncResult`1 result)

Exception rethrown at [0]:
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at Microsoft.Mashup.OleDbProvider.MashupRowset.get__Rowset()
at Microsoft.Mashup.OleDbProvider.MashupRowset.get_ColumnsInfo()
at Microsoft.OleDb.Rowset.Microsoft.OleDb.IColumnsInfo.GetColumnInfo(DBORDINAL& countColumnInfos, DBCOLUMNINFO*& nativeColumnInfos, Char*& nativeStrings)
at Microsoft.Mashup.OleDbProvider.TracingRowset.Microsoft.OleDb.IColumnsInfo.GetColumnInfo(DBORDINAL& countColumnInfos, DBCOLUMNINFO*& nativeColumnInfos, Char*& nativeStrings)

Stack Trace Message:
Container exited unexpectedly with code 0xFFFFFFFF. PID: 12464.
Used features: (none).

Invocation Stack Trace:
at Microsoft.Mashup.Host.Document.ExceptionExtensions.GetCurrentInvocationStackTrace()
at Microsoft.Mashup.Client.UI.Shared.FeedbackErrorInfo..ctor(String message, Exception exception, Nullable`1 stackTraceInfo, String messageDetail)
at Microsoft.Mashup.Client.UI.Windows.UIHost.RaiseErrorDialog(IWindowHandle activeWindow, FeedbackPackageInfo feedbackPackageInfo, Exception e, LocalizedString dialogTitle, LocalizedString dialogMessage, Boolean useGDICapture)
at Microsoft.Mashup.Client.Excel.Native.NativeUserFeedbackServices.ReportException(IWindowHandle activeWindow, IUIHost uiHost, FeedbackPackageInfo feedbackPackageInfo, Exception e, Boolean useGDICapture)
at Microsoft.Mashup.Client.UI.Shared.UnexpectedExceptionHandler.<>c__DisplayClass14_0.<HandleException>b__0()
at Microsoft.Mashup.Client.UI.Shared.UnexpectedExceptionHandler.HandleException(Exception e)
at Microsoft.Mashup.Client.Excel.OleDbProvider.ExcelMashupClassFactory.TraceAndReportException(String method, Exception exception)
at Microsoft.Mashup.OleDbProvider.TracingRowset.Microsoft.OleDb.IColumnsInfo.GetColumnInfo(DBORDINAL& countColumnInfos, DBCOLUMNINFO*& nativeColumnInfos, Char*& nativeStrings)


InnerException0.Stack Trace Message:
Container exited unexpectedly with code 0xFFFFFFFF. PID: 12464.

InnerException0.Stack Trace:
at Microsoft.Mashup.Evaluator.ErrorTranslatingMessenger.MessageChannel.Read()
at Microsoft.Mashup.Evaluator.ErrorTranslatingMessenger.MessageChannel.Read()

InnerException0.Invocation Stack Trace:
at Microsoft.Mashup.Host.Document.ExceptionExtensions.GetCurrentInvocationStackTrace()
at Microsoft.Mashup.Client.UI.Shared.FeedbackErrorInfo.GetFeedbackValuesFromException(Exception e, String prefix)
at Microsoft.Mashup.Client.UI.Shared.FeedbackErrorInfo.GetFeedbackValuesFromInnerExceptions(Exception e, Int32 depth)
at Microsoft.Mashup.Client.UI.Shared.FeedbackErrorInfo.CreateAdditionalErrorInfo(Exception e)
at Microsoft.Mashup.Client.UI.Shared.FeedbackErrorInfo..ctor(String message, Exception exception, Nullable`1 stackTraceInfo, String messageDetail)
at Microsoft.Mashup.Client.UI.Windows.UIHost.RaiseErrorDialog(IWindowHandle activeWindow, FeedbackPackageInfo feedbackPackageInfo, Exception e, LocalizedString dialogTitle, LocalizedString dialogMessage, Boolean useGDICapture)
at Microsoft.Mashup.Client.Excel.Native.NativeUserFeedbackServices.ReportException(IWindowHandle activeWindow, IUIHost uiHost, FeedbackPackageInfo feedbackPackageInfo, Exception e, Boolean useGDICapture)
at Microsoft.Mashup.Client.UI.Shared.UnexpectedExceptionHandler.<>c__DisplayClass14_0.<HandleException>b__0()
at Microsoft.Mashup.Client.UI.Shared.UnexpectedExceptionHandler.HandleException(Exception e)
at Microsoft.Mashup.Client.Excel.OleDbProvider.ExcelMashupClassFactory.TraceAndReportException(String method, Exception exception)
at Microsoft.Mashup.OleDbProvider.TracingRowset.Microsoft.OleDb.IColumnsInfo.GetColumnInfo(DBORDINAL& countColumnInfos, DBCOLUMNINFO*& nativeColumnInfos, Char*& nativeStrings)


Supports Premium Content:
False

Formulas:


section Section1;

shared #"EQ 3 COD" = let
Source = Excel.CurrentWorkbook(){[Name="Table4"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Process Value", Int64.Type}, {"Parameter", type text}, {"Unit Process", type text}})
in
#"Changed Type";

shared #"EQ 3 TSS" = let
Source = Excel.CurrentWorkbook(){[Name="EQ3TSS"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Process Value", Int64.Type}, {"Parameter", type text}, {"Unit Process", type text}})
in
#"Changed Type";

shared #"MCH 25 Capacity" = let
Source = Excel.CurrentWorkbook(){[Name="Table6"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Process Value", type any}, {"Parameter", type text}, {"Unit Process", type text}})
in
#"Changed Type";

shared #"MCH 25 pH" = let
Source = Excel.CurrentWorkbook(){[Name="Table7"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Process Value", type number}, {"Parameter", type text}, {"Unit Process", type text}})
in
#"Changed Type";

shared #"MCH 25 COD" = let
Source = Excel.CurrentWorkbook(){[Name="Table8"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Process Value", Int64.Type}, {"Parameter", type text}, {"Unit Process", type text}})
in
#"Changed Type";

shared #"MCH 25 TSS" = let
Source = Excel.CurrentWorkbook(){[Name="Table9"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type datetime}, {"Process Value", Int64.Type}, {"Parameter", type text}, {"Unit Process", type text}})
in
#"Changed Type";

shared #"MCH 25 N" = let
Source = Excel.CurrentWorkbook(){[Name="Table10"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Process Value", type any}, {"Parameter", type text}, {"Unit Process", type text}})
in
#"Changed Type";

shared #"MCH 25 P" = let
Source = Excel.CurrentWorkbook(){[Name="Table11"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Process Value", type any}, {"Parameter", type text}, {"Unit Process", type text}})
in
#"Changed Type";

shared #"SBR 1 pH" = let
Source = Excel.CurrentWorkbook(){[Name="Table12"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Process Value", type number}, {"Parameter", type text}, {"Unit Process", type text}})
in
#"Changed Type";

shared #"SBR 1 DO" = let
Source = Excel.CurrentWorkbook(){[Name="Table14"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Process Value", type number}, {"Parameter", type text}, {"Unit Process", type text}})
in
#"Changed Type";

shared #"SBR 1 MLSS" = let
Source = Excel.CurrentWorkbook(){[Name="Table15"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Process Value", Int64.Type}, {"Parameter", type text}, {"Unit Process", type text}})
in
#"Changed Type";

shared #"SBR 1 SV30" = let
Source = Excel.CurrentWorkbook(){[Name="Table16"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Process Value", Int64.Type}, {"Parameter", type text}, {"Unit Process", type text}})
in
#"Changed Type";

shared #"SBR 1 SVI" = let
Source = Excel.CurrentWorkbook(){[Name="Table17"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Process Value", type any}, {"Parameter", type text}, {"Unit Process", type text}})
in
#"Changed Type";

shared #"SBR 1 F/M" = let
Source = Excel.CurrentWorkbook(){[Name="Table18"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Process Value", type any}, {"Parameter", type text}, {"Unit Process", type text}})
in
#"Changed Type";

shared #"SBR 1 SOUR" = let
Source = Excel.CurrentWorkbook(){[Name="Table182"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Process Value", type any}, {"Parameter", type text}, {"Unit Process", type text}})
in
#"Changed Type";

shared #"SBR 2 pH" = let
Source = Excel.CurrentWorkbook(){[Name="Table19"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Process Value", type number}, {"Parameter", type text}, {"Unit Process", type text}})
in
#"Changed Type";

shared #"SBR 2 DO" = let
Source = Excel.CurrentWorkbook(){[Name="Table20"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Process Value", type number}, {"Parameter", type text}, {"Unit Process", type text}})
in
#"Changed Type";

shared #"SBR 2 MLSS" = let
Source = Excel.CurrentWorkbook(){[Name="Table21"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Process Value", Int64.Type}, {"Parameter", type text}, {"Unit Process", type text}})
in
#"Changed Type";

shared #"SBR 2 SV30" = let
Source = Excel.CurrentWorkbook(){[Name="Table22"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Process Value", Int64.Type}, {"Parameter", type text}, {"Unit Process", type text}})
in
#"Changed Type";

shared #"SBR 2 SVI" = let
Source = Excel.CurrentWorkbook(){[Name="Table23"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Process Value", type any}, {"Parameter", type text}, {"Unit Process", type text}})
in
#"Changed Type";

shared #"SBR 2 F/M" = let
Source = Excel.CurrentWorkbook(){[Name="Table24"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Process Value", type any}, {"Parameter", type text}, {"Unit Process", type text}})
in
#"Changed Type";

shared #"SBR 2 SOUR" = let
Source = Excel.CurrentWorkbook(){[Name="Table1824"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Process Value", type any}, {"Parameter", type text}, {"Unit Process", type text}})
in
#"Changed Type";

shared #"AP pH" = let
Source = Excel.CurrentWorkbook(){[Name="Table25"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Process Value", type number}, {"Parameter", type text}, {"Unit Process", type text}})
in
#"Changed Type";

shared #"AP COD" = let
Source = Excel.CurrentWorkbook(){[Name="Table26"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Process Value", Int64.Type}, {"Parameter", type text}, {"Unit Process", type text}})
in
#"Changed Type";

shared #"AP DO P1" = let
Source = Excel.CurrentWorkbook(){[Name="Table27"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Process Value", type number}, {"Parameter", type text}, {"Unit Process", type text}})
in
#"Changed Type";

shared #"AP DO P2" = let
Source = Excel.CurrentWorkbook(){[Name="Table28"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Process Value", type number}, {"Parameter", type text}, {"Unit Process", type text}})
in
#"Changed Type";

shared #"AP DO P3" = let
Source = Excel.CurrentWorkbook(){[Name="Table29"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Process Value", type number}, {"Parameter", type text}, {"Unit Process", type text}})
in
#"Changed Type";

shared #"AP DO P4" = let
Source = Excel.CurrentWorkbook(){[Name="Table30"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Process Value", type number}, {"Parameter", type text}, {"Unit Process", type text}})
in
#"Changed Type";

shared #"AP MLSS" = let
Source = Excel.CurrentWorkbook(){[Name="Table31"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Process Value", Int64.Type}, {"Parameter", type text}, {"Unit Process", type text}})
in
#"Changed Type";

shared #"AP SV30" = let
Source = Excel.CurrentWorkbook(){[Name="Table32"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Process Value", Int64.Type}, {"Parameter", type text}, {"Unit Process", type text}})
in
#"Changed Type";

shared #"AP SVI" = let
Source = Excel.CurrentWorkbook(){[Name="Table33"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Process Value", type any}, {"Parameter", type text}, {"Unit Process", type text}})
in
#"Changed Type";

shared #"AP F/M" = let
Source = Excel.CurrentWorkbook(){[Name="Table34"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Process Value", type any}, {"Parameter", type text}, {"Unit Process", type text}})
in
#"Changed Type";

shared #"AP SOUR" = let
Source = Excel.CurrentWorkbook(){[Name="Table182442"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Process Value", type any}, {"Parameter", type text}, {"Unit Process", type text}})
in
#"Changed Type";

shared #"SPC 33 Capacity" = let
Source = Excel.CurrentWorkbook(){[Name="Table35"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Process Value", Int64.Type}, {"Parameter", type text}, {"Unit Process", type text}})
in
#"Changed Type";

shared #"FD Capacity" = let
Source = Excel.CurrentWorkbook(){[Name="Table36"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Process Value", Int64.Type}, {"Parameter", type text}, {"Unit Process", type text}})
in
#"Changed Type";

shared #"FD pH" = let
Source = Excel.CurrentWorkbook(){[Name="Table37"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Process Value", type number}, {"Parameter", type text}, {"Unit Process", type text}})
in
#"Changed Type";

shared #"FD COD" = let
Source = Excel.CurrentWorkbook(){[Name="Table38"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Process Value", Int64.Type}, {"Parameter", type text}, {"Unit Process", type text}})
in
#"Changed Type";

shared #"FD TSS" = let
Source = Excel.CurrentWorkbook(){[Name="Table39"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Process Value", Int64.Type}, {"Parameter", type text}, {"Unit Process", type text}})
in
#"Changed Type";

shared #"FT Dryness" = let
Source = Excel.CurrentWorkbook(){[Name="Table40"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Process Value", type any}, {"Parameter", type text}, {"Unit Process", type text}})
in
#"Changed Type";

shared #"FT Sludge Quantity" = let
Source = Excel.CurrentWorkbook(){[Name="Table41"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Process Value", type any}, {"Parameter", type text}, {"Unit Process", type text}})
in
#"Changed Type";

shared #"Performance Monitoring" = let
Source = Table.Combine({#"EQ 3 COD", #"EQ 3 TSS", #"MCH 25 Capacity", #"MCH 25 pH", #"MCH 25 COD", #"MCH 25 TSS", #"MCH 25 N", #"MCH 25 P", #"SBR 1 pH", #"SBR 1 DO", #"SBR 1 MLSS", #"SBR 1 SV30", #"SBR 1 SVI", #"SBR 1 F/M", #"SBR 1 SOUR", #"SBR 2 pH", #"SBR 2 DO", #"SBR 2 MLSS", #"SBR 2 SV30", #"SBR 2 SVI", #"SBR 2 F/M", #"SBR 2 SOUR", #"AP pH", #"AP COD", #"AP DO P1", #"AP DO P2", #"AP DO P3", #"AP DO P4", #"AP MLSS", #"AP SV30", #"AP SVI", #"AP F/M", #"AP SOUR", #"SPC 33 Capacity", #"FD Capacity", #"FD pH", #"FD COD", #"FD TSS", #"FT Dryness", #"FT Sludge Quantity"}),
#"Filtered Rows" = Table.SelectRows(Source, each true)
in
#"Filtered Rows";

shared PAC = let
Source = Excel.CurrentWorkbook(){[Name="Table42"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Process Value", type number}, {"Parameter", type text}, {"Unit Process", type text}})
in
#"Changed Type";

shared #"A Polymer" = let
Source = Excel.CurrentWorkbook(){[Name="Table4245"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Process Value", Int64.Type}, {"Parameter", type text}, {"Unit Process", type text}})
in
#"Changed Type";

shared #"C Polymer" = let
Source = Excel.CurrentWorkbook(){[Name="Table4246"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Process Value", Int64.Type}, {"Parameter", type text}, {"Unit Process", type text}})
in
#"Changed Type";

shared #"COD Reagent" = let
Source = Excel.CurrentWorkbook(){[Name="Table4247"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Process Value", Int64.Type}, {"Parameter", type text}, {"Unit Process", type text}})
in
#"Changed Type";

shared Urea = let
Source = Excel.CurrentWorkbook(){[Name="Table4248"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Process Value", Int64.Type}, {"Parameter", type text}, {"Unit Process", type text}})
in
#"Changed Type";

shared DAP = let
Source = Excel.CurrentWorkbook(){[Name="Table4249"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Process Value", Int64.Type}, {"Parameter", type text}, {"Unit Process", type text}})
in
#"Changed Type";

shared #"N Reagent" = let
Source = Excel.CurrentWorkbook(){[Name="Table4250"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Process Value", Int64.Type}, {"Parameter", type text}, {"Unit Process", type text}})
in
#"Changed Type";

shared #"B Reagent" = let
Source = Excel.CurrentWorkbook(){[Name="Table4251"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Process Value", Int64.Type}, {"Parameter", type text}, {"Unit Process", type text}})
in
#"Changed Type";

shared Defoamer = let
Source = Excel.CurrentWorkbook(){[Name="Table4252"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Process Value", Int64.Type}, {"Parameter", type text}, {"Unit Process", type text}})
in
#"Changed Type";

shared #"P Reagent" = let
Source = Excel.CurrentWorkbook(){[Name="Table4253"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Process Value", type number}, {"Parameter", type text}, {"Unit Process", type text}})
in
#"Changed Type";

shared #"Chemical Consumption" = let
Source = Table.Combine({#"MCH 25 Capacity", #"SPC 33 Capacity", #"FD Capacity", PAC, #"A Polymer", #"C Polymer", #"COD Reagent", Urea, DAP, #"N Reagent", #"B Reagent", Defoamer, #"P Reagent"})
in
Source;

shared #"DUL EQ COD" = let
Source = Excel.CurrentWorkbook(){[Name="Table444"]}[Content]
in
Source;

shared #"DUL EQ TSS" = let
Source = Excel.CurrentWorkbook(){[Name="EQ3TSS54"]}[Content]
in
Source;

shared #"DUL MCH25 Capacity" = let
Source = Excel.CurrentWorkbook(){[Name="Table655"]}[Content]
in
Source;

shared #"DUL MCH25 pH" = let
Source = Excel.CurrentWorkbook(){[Name="Table756"]}[Content]
in
Source;

shared #"DLL MCH25 pH" = let
Source = Excel.CurrentWorkbook(){[Name="Table75693"]}[Content]
in
Source;

shared #"DUL MCH25 COD" = let
Source = Excel.CurrentWorkbook(){[Name="Table857"]}[Content]
in
Source;

shared #"DUL MCH25 TSS" = let
Source = Excel.CurrentWorkbook(){[Name="Table958"]}[Content]
in
Source;

shared #"DUL MCH25 N" = let
Source = Excel.CurrentWorkbook(){[Name="Table1059"]}[Content]
in
Source;

shared #"DUL MCH25 P" = let
Source = Excel.CurrentWorkbook(){[Name="Table1160"]}[Content]
in
Source;

shared #"DUL SBR pH" = let
Source = Excel.CurrentWorkbook(){[Name="Table1261"]}[Content]
in
Source;

shared #"DLL SBR pH" = let
Source = Excel.CurrentWorkbook(){[Name="Table126194"]}[Content]
in
Source;

shared #"DUL SBR DO" = let
Source = Excel.CurrentWorkbook(){[Name="Table1462"]}[Content]
in
Source;

shared #"DLL SBR DO" = let
Source = Excel.CurrentWorkbook(){[Name="Table146295"]}[Content]
in
Source;

shared #"DUL SBR MLSS" = let
Source = Excel.CurrentWorkbook(){[Name="Table1563"]}[Content]
in
Source;

shared #"DLL SBR MLSS" = let
Source = Excel.CurrentWorkbook(){[Name="Table156396"]}[Content]
in
Source;

shared #"DUL SBR SV30" = let
Source = Excel.CurrentWorkbook(){[Name="Table1664"]}[Content]
in
Source;

shared #"DLL SBR SV30" = let
Source = Excel.CurrentWorkbook(){[Name="Table166497"]}[Content]
in
Source;

shared #"DUL SBR SVI" = let
Source = Excel.CurrentWorkbook(){[Name="Table1765"]}[Content]
in
Source;

shared #"DLL SBR SVI" = let
Source = Excel.CurrentWorkbook(){[Name="Table176598"]}[Content]
in
Source;

shared #"DUL SBR FM" = let
Source = Excel.CurrentWorkbook(){[Name="Table1866"]}[Content]
in
Source;

shared #"DLL SBR FM" = let
Source = Excel.CurrentWorkbook(){[Name="Table186699"]}[Content]
in
Source;

shared #"DUL SBR SOUR" = let
Source = Excel.CurrentWorkbook(){[Name="Table18290"]}[Content]
in
Source;

shared #"DLL SBR SOUR" = let
Source = Excel.CurrentWorkbook(){[Name="Table18290100"]}[Content]
in
Source;

shared #"DUL AP pH" = let
Source = Excel.CurrentWorkbook(){[Name="Table2573"]}[Content]
in
Source;

shared #"DLL AP pH" = let
Source = Excel.CurrentWorkbook(){[Name="Table2573101"]}[Content]
in
Source;

shared #"DUL AP COD" = let
Source = Excel.CurrentWorkbook(){[Name="Table2674"]}[Content]
in
Source;

shared #"DUL AP DO" = let
Source = Excel.CurrentWorkbook(){[Name="Table146267"]}[Content]
in
Source;

shared #"DLL AP DO" = let
Source = Excel.CurrentWorkbook(){[Name="Table14629575"]}[Content]
in
Source;

shared #"DUL AP MLSS" = let
Source = Excel.CurrentWorkbook(){[Name="Table156368"]}[Content]
in
Source;

shared #"DLL AP MLSS" = let
Source = Excel.CurrentWorkbook(){[Name="Table15639676"]}[Content]
in
Source;

shared #"DUL AP SV30" = let
Source = Excel.CurrentWorkbook(){[Name="Table166469"]}[Content]
in
Source;

shared #"DLL AP SV30" = let
Source = Excel.CurrentWorkbook(){[Name="Table16649777"]}[Content]
in
Source;

shared #"DUL AP SVI" = let
Source = Excel.CurrentWorkbook(){[Name="Table176570"]}[Content]
in
Source;

shared #"DLL AP SVI" = let
Source = Excel.CurrentWorkbook(){[Name="Table17659878"]}[Content]
in
Source;

shared #"DUL AP FM" = let
Source = Excel.CurrentWorkbook(){[Name="Table186671"]}[Content]
in
Source;

shared #"DLL AP FM" = let
Source = Excel.CurrentWorkbook(){[Name="Table18669979"]}[Content]
in
Source;

shared #"DUL AP SOUR" = let
Source = Excel.CurrentWorkbook(){[Name="Table1829072"]}[Content]
in
Source;

shared #"DLL AP SOUR" = let
Source = Excel.CurrentWorkbook(){[Name="Table1829010080"]}[Content]
in
Source;

shared #"DUL SPC33 Capacity" = let
Source = Excel.CurrentWorkbook(){[Name="Table3583"]}[Content]
in
Source;

shared #"DUL FD pH" = let
Source = Excel.CurrentWorkbook(){[Name="Table3785"]}[Content]
in
Source;

shared #"DLL FD pH" = let
Source = Excel.CurrentWorkbook(){[Name="Table378581"]}[Content]
in
Source;

shared #"DUL FD COD" = let
Source = Excel.CurrentWorkbook(){[Name="Table3886"]}[Content]
in
Source;

shared #"DUL FD TSS" = let
Source = Excel.CurrentWorkbook(){[Name="Table3987"]}[Content]
in
Source;

shared #"DLL FT Dryness" = let
Source = Excel.CurrentWorkbook(){[Name="Table4088"]}[Content]
in
Source;

shared #"DUL FT Qty" = let
Source = Excel.CurrentWorkbook(){[Name="Table4189"]}[Content]
in
Source;

shared #"Design Limit" = let
Source = Table.Combine({#"DUL EQ COD", #"DUL EQ TSS", #"DUL MCH25 Capacity", #"DUL MCH25 pH", #"DLL MCH25 pH", #"DUL MCH25 COD", #"DUL MCH25 TSS", #"DUL MCH25 N", #"DUL MCH25 P", #"DUL SBR pH", #"DLL SBR pH", #"DUL SBR DO", #"DLL SBR DO", #"DUL SBR MLSS", #"DLL SBR MLSS", #"DUL SBR SV30", #"DLL SBR SV30", #"DUL SBR SVI", #"DLL SBR SVI", #"DUL SBR FM", #"DLL SBR FM", #"DUL SBR SOUR", #"DLL SBR SOUR", #"DUL AP pH", #"DLL AP pH", #"DUL AP COD", #"DUL AP DO", #"DLL AP DO", #"DUL AP MLSS", #"DLL AP MLSS", #"DUL AP SV30", #"DLL AP SV30", #"DUL AP SVI", #"DLL AP SVI", #"DUL AP FM", #"DLL AP FM", #"DUL AP SOUR", #"DLL AP SOUR", #"DUL SPC33 Capacity", #"DUL FD pH", #"DLL FD pH", #"DUL FD COD", #"DUL FD TSS", #"DLL FT Dryness", #"DUL FT Qty"})
in
Source;

shared DPM = let
Source = Table.Combine({#"Performance Monitoring", #"Design Limit"})
in
Source;

shared #"DPM 1" = let
Source = Table.Combine({#"Performance Monitoring", #"Design Limit"})
in
Source;

shared #"DPM (2)" = let
Source = Table.Combine({#"Performance Monitoring", #"Design Limit"})
in
Source;

 

Thanks in advance.

10 Replies

@Azizol 

Such error could appear due to different reasons, hard to say without the file. Try to clean the cache. Check and clean your queries, e.g. in

===

shared #"MCH 25 Capacity" = let
Source = Excel.CurrentWorkbook(){[Name="Table6"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Process Value", type any}, {"Parameter", type text}, {"Unit Process", type text}})
in
#"Changed Type";

shared #"MCH 25 pH" = let
Source = Excel.CurrentWorkbook(){[Name="Table7"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Process Value", type number}, {"Parameter", type text}, {"Unit Process", type text}})
in
#"Changed Type";

shared #"MCH 25 COD" = let
Source = Excel.CurrentWorkbook(){[Name="Table8"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Process Value", Int64.Type}, {"Parameter", type text}, {"Unit Process", type text}})
in
#"Changed Type";

===

the same filed is transformed into different data types and combine on later steps. Try to remove all Changed Type or apply same types to the fields which will be combined in one.

@Sergei Baklan 

Thanks for the suggestion. I've tried to clear all Changed Type but the problem still persist. Here I attach the file. Hope you can have better understanding to pinpoint the reason.

anyway how to clean the cache? never done this before
best response confirmed by Azizol (Copper Contributor)
Solution

@Azizol 

Clear cache is at Data->Get Data->Query Options

image.png

If I refresh your file I have no errors with the engine, but query itself returns errors:

image.png

If to check

image.png

and this query takes data from

image.png

Select Table6

image.png

Here N/A is returned by design

image.png

If we need it here, I guess for the chart, in Power Query it's better to replace errors on nulls or like.

 

But again, I have no errors on refresh with this file.

@Sergei Baklan 

Thanks for helping. Is there a possibility that problem I'm facing currently due to external cause? Like windows update or computer drive?

@Sergei Baklan

I tried to reset my PC and it worked out. Still don't know though what is the main cause. Anyway thanks a lot for helping me.

@Azizol 

Good to know you sorted it out. That could be quite many reasons for such error, if to simplify actually it only says "Something is going wrong".

Hi Azizol,

Could you please tell what exactly you reset and how reset so I can also resolve the issue. Kindly elaborate it. Its a request to you. Thank you in advance.

@Azizol I am facing the same issue, i made a connection to microsoft sql server and here are 4 regison tables. for 3 regions pivot data and sql server data is fetched properly but for 4th region is it giving me error.  for some tables it works but for others it does not.

 

Could you please tell me how to resolve this issue without resetting the PC.

@get_chandrakant Unfortunately I resolve my case by resetting my PC and I also didn't know the main cause. Sorry for not being able to help.

1 best response

Accepted Solutions
best response confirmed by Azizol (Copper Contributor)
Solution

@Azizol 

Clear cache is at Data->Get Data->Query Options

image.png

If I refresh your file I have no errors with the engine, but query itself returns errors:

image.png

If to check

image.png

and this query takes data from

image.png

Select Table6

image.png

Here N/A is returned by design

image.png

If we need it here, I guess for the chart, in Power Query it's better to replace errors on nulls or like.

 

But again, I have no errors on refresh with this file.

View solution in original post