Issue with Custom column in PowerQuery

Anonymous · ‎06-07-2018

Hi,

In my PowerQuery i have the following code

Table.AddColumn(tb_Pers_Table, "CustomSurname", each Text.Combine(List.RemoveItems(Text.ToList([Surname]),Text.ToList(",.';")))) & " " & [CH_Name_Initials]

However on running I get the following error.

Object doesnt support property or method 'scrollColumnIntoViewandFocus'

Then the following details.

Feedback Type:
Frown (Error)

Error Message:
Object doesn't support property or method 'scrollColumnIntoViewAndFocus'

Stack Trace:
   at Microsoft.Mashup.Client.UI.Windows.JsErrorHandler.WrapInvokeScript(Func`1 invokeScript)
   at Microsoft.Mashup.Client.UI.Shared.Com.ComJsObject.Invoke[T](String methodName, Object[] args)
   at Microsoft.Mashup.Client.UI.Shared.Com.ComJsObject.TryInvoke[T](String methodName, Object[] args, T& result)
   at Microsoft.Mashup.Client.UI.Shared.IJsObjectExtensions.TryInvoke(IJsObject jsObject, String methodName, Object[] args)
   at Microsoft.Mashup.Client.UI.Shared.HtmlControls.Results.QueryResultContainerHtmlControl.<>c__DisplayClass4.<>c__DisplayClass6.<EvaluateAndRenderQueryResult>b__2()
   at Microsoft.Mashup.Client.UI.Shared.HtmlControls.Results.Grid.GridResultHtmlControl.<OnViewportResize>b__2(Int32 numberOfRowsRetrieved)
   at Microsoft.Mashup.Client.UI.Shared.HtmlControls.Results.Grid.GridResultHtmlControl.<>c__DisplayClass27.<RetrieveRowsAsync>b__20(PreviewReference previewReference, IList`1 rows)
   at Microsoft.Mashup.Client.UI.Shared.Models.Results.Record.RecordRowProvider.TryHandleGridResult(PreviewReference previewReference, FormulaResult formulaResult, Action`2 onSuccess)
   at Microsoft.Mashup.Client.UI.Shared.Models.Results.GridRowProvider.OnEvaluationCompleted(PreviewReference previewReference, Action`2 onSuccess, Action`1 onException)
   at Microsoft.Mashup.Host.Document.Evaluation.PreviewEvaluationQueue.<>c__DisplayClass25.<OnPreviewComplete>b__23()
   at Microsoft.Mashup.Host.Document.ExceptionHandlerExtensions.HandleExceptions(IExceptionHandler exceptionHandler, Action action)

Stack Trace Message:
Object doesn't support property or method 'scrollColumnIntoViewAndFocus'

Invocation Stack Trace:
   at Microsoft.Mashup.Host.Document.ExceptionExtensions.GetCurrentInvocationStackTrace()
   at Microsoft.Mashup.Client.UI.Shared.FeedbackErrorInfo..ctor(String message, Exception exception)
   at Microsoft.PowerBI.Client.Windows.Telemetry.PowerBIUserFeedbackServices.ReportException(IWindowHandle activeWindow, IUIHost uiHost, FeedbackPackageInfo feedbackPackageInfo, Exception e, Boolean useGDICapture)
   at Microsoft.Mashup.Client.UI.Shared.UnexpectedExceptionHandler.<>c__DisplayClass1.<HandleException>b__0()
   at Microsoft.Mashup.Client.UI.Shared.UnexpectedExceptionHandler.HandleException(Exception e)
   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.UI.Shared.WebDialogs.WebDialog.<>n__FabricatedMethod8(IWindowHandle )
   at Microsoft.Mashup.Client.UI.Shared.WindowManager.ShowModal[T](T dialog, Func`1 showModalFunction)
   at Microsoft.PowerBI.Client.Program.<>c__DisplayClassb.<Main>b__0()
   at Microsoft.PowerBI.Client.Windows.IExceptionHandlerExtensions.<>c__DisplayClass7.<HandleExceptionsWithNestedTasks>b__6()
   at Microsoft.Mashup.Host.Document.ExceptionHandlerExtensions.HandleExceptions(IExceptionHandler exceptionHandler, Action action)
   at Microsoft.PowerBI.Client.Program.Main(String[] args)


Model Default Mode:
Import

Snapshot Trace Logs:
C:\Users\cjack\AppData\Local\Microsoft\Power BI Desktop\FrownSnapShot1367637926.zip

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

Enabled Preview Features:
PBI_shapeMapVisualEnabled
PBI_EnableReportTheme
PBI_newFromWeb
PBI_qnaExplore
PBI_canvasTooltips

Disabled Preview Features:
PBI_SpanishLinguisticsEnabled
CustomConnectors
PBI_variationUIChange
PBI_showIncrementalRefreshPolicy

Disabled DirectQuery Options:
PBI_DirectQuery_Unrestricted
TreatHanaAsRelationalSource

Cloud:
GlobalCloud

Formulas:


section Section1;

shared #"Claims Database" = let
    Source = Odbc.Query("dsn=Holiday Database", "Select#(lf)    T1.ASSR_FEE as AssessorFee,#(lf)    T1.cl_id_ecm as ReferenceId,#(lf)    T1.cl_ID as ClaimID,#(lf)    T1.Load_no as LoadNumber,#(lf)    T2.HGV_exp as HGVExpirary,#(lf)    IIF(max(T11.Inv_ref) <> NULL, 'Yes', 'No') as Invoiced,#(lf)    T1.CAU_DT as Comment,#(lf)    max(T13.Full_Desc) as MakeofVehicle,#(lf)    max(T14.Full_Desc) as ClassofVehicle,   #(lf)    T1.CH_NO as ChassisNo,#(lf)    T1.Incident_Date as IncidentDate,#(lf)    T1.Inv_amount as InvoiceAmount,#(lf)    T1.Rep_Cost as RepCost,#(lf)    t1.Est_cost_init as EstimatedCost,#(lf)    T1.cldeta_dealer0 as Dealercode,#(lf)    t6.name as DealerName,#(lf)    T1.pers_no as PersonRef,#(lf)    T2.CO_Name & ' ' & T2.Surname  as DriverName,#(lf)    T3.CO_Name & ' ' & T3.Surname as InspectorName,#(lf)    max(T7.Full_Desc) as Customer,#(lf)    max(T8.Full_Desc) as CollectionPoint,#(lf)    max(T9.Full_Desc) as MainDescription,#(lf)    max(T10.Full_Desc) as Subdescription,#(lf)    max(T12.Full_Desc) as Invoice   #(lf)FROM#(lf)    ((((((((((((cl_details T1)#(lf)    LEFT OUTER JOIN [C:\Users\cjack\Desktop\Test Stuff\ecm_hol.mdb].tb_pers T2 on (T1.pers_no = T2.ref_id))#(lf)    LEFT OUTER JOIN [C:\Users\cjack\Desktop\Test Stuff\ecm_hol.mdb].tb_pers T3 on (T1.insp_id = T3.ref_id))#(lf)    LEFT OUTER JOIN cl_coding T4 on (T1.cl_id = T4.cl_id))#(lf)    Left outer join Dealers T6 on (T1.cldeta_dealer0 = T6.dealer0))#(lf)    Left Outer Join Sy_code_list T7 on (t4.ID_Code = t7.ID_Code and t4.Level_2 = t7.Level_2 and t7.ID_Code = 2 and (t7.Level_3 = 0 or t7.Level_3 = -1)))#(lf)    Left Outer Join Sy_code_list T8 on (t4.ID_Code = t8.ID_Code and t4.Level_2 = t8.Level_2 and t8.ID_Code = 11 and (t8.Level_3 = 0 or t8.Level_3 = -1)))#(lf)    Left Outer Join Sy_code_list T13 on (t4.ID_Code = T13.ID_Code and t4.Level_2 = T13.Level_2 and T13.ID_Code = 1 and (T13.Level_3 = 0 or T13.Level_3 = -1)))#(lf)    Left Outer Join Sy_code_list T12 on (t4.ID_Code = t12.ID_Code and t4.Level_2 = t12.Level_2 and t4.Level_3 = t12.Level_3 and t12.ID_Code = 8 and (t12.Level_4 = 0 or t12.Level_4 = -1)))#(lf)    Left Outer Join Sy_code_list T9 on (t4.ID_Code = t9.ID_Code and t4.Level_2  = t9.Level_2 and t9.ID_Code = 5 and (t9.Level_3 = 0 or t9.Level_3 = -1)))#(lf)    Left Outer Join Sy_code_list T10 on (t4.ID_Code = t10.ID_Code and t4.Level_2 = t10.Level_2 and t4.Level_3 = t10.Level_3 and t10.ID_Code = 5 and (t10.Level_4 = 0 or t10.Level_4 = -1)))#(lf)    Left Outer Join Sy_code_list T14 on (t4.ID_Code = T14.ID_Code and t4.Level_2 = T14.Level_2 and t4.Level_3 = T14.Level_3 and T14.ID_Code = 1 and (T14.Level_4 = 0 or T14.Level_4 = -1)))#(lf)    left outer join Da_inv t11 on (t1.cl_id = t11.cl_id)#(lf)Group By#(lf)    T1.ASSR_FEE,#(lf)    T1.cl_ID,#(lf)    T1.CAU_DT,#(lf)    T1.CH_NO,#(lf)    T2.HGV_exp,#(lf)    T1.Load_no,#(lf)    T1.Incident_Date,#(lf)    T1.Inv_amount,#(lf)    T1.pers_no,#(lf)    T1.Rep_Cost,#(lf)    T1.cl_id_ecm,#(lf)    t1.Est_cost_init,#(lf)    T1.cldeta_dealer0,#(lf)    t6.name,#(lf)    T2.CO_Name & ' ' & T2.Surname,#(lf)    T3.CO_Name & ' ' & T3.Surname"),
    #"Added Conditional Column" = Table.AddColumn(Source, "Custom", each if [DriverName] = """""" then "Subcontractor" else "ECM")
in
    #"Added Conditional Column";

shared Damage = let
    Source = Odbc.Query("dsn=Holiday Database", "Select#(lf)    T1.cl_ID as ClaimID,#(lf)    T1.inv_amount,#(lf)    (T11.Full_Desc) as Damage1,#(lf)    (T12.Full_Desc) as Damage2#(lf)#(lf)FROM#(lf)    (((cl_details T1)#(lf)    LEFT OUTER JOIN cl_coding T4 on (T1.cl_id = T4.cl_id))#(lf)    Left Outer Join Sy_code_list T11 on (t4.ID_Code = t11.ID_Code and t4.Level_2  = t11.Level_2 and t11.ID_Code = 4 and (t11.Level_3 = 0 or t11.Level_3 = -1)))#(lf)    Left Outer Join Sy_code_list T12 on (t4.ID_Code = t12.ID_Code and t4.Level_2 = t12.Level_2 and t4.Level_3 = t12.Level_3 and t12.ID_Code = 4 and (t12.Level_4 = 0 or t12.Level_4 = -1))#(lf)WHERE#(lf)#(lf)T11.Full_Desc <> NULL#(lf)#(lf)Group By#(lf)    T1.cl_ID,#(lf)    (T11.Full_Desc),#(lf)    (T12.Full_Desc),#(lf)     T1.inv_amount#(lf)")
in
    Source;

shared Tb_per_addr = let
    Source = Odbc.DataSource("dsn=Holiday Database2", [HierarchicalNavigation=true]),
    #"Y:\ecm_hol.mdb_Database" = Source{[Name="Y:\ecm_hol.mdb",Kind="Database"]}[Data],
    Tb_per_addr_Table = #"Y:\ecm_hol.mdb_Database"{[Name="Tb_per_addr",Kind="Table"]}[Data]
in
    Tb_per_addr_Table;

shared tb_Pers = let
    Source = Odbc.DataSource("dsn=Holiday Database2", [HierarchicalNavigation=true]),
    #"Y:\ecm_hol.mdb_Database" = Source{[Name="Y:\ecm_hol.mdb",Kind="Database"]}[Data],
    tb_Pers_Table = #"Y:\ecm_hol.mdb_Database"{[Name="tb_Pers",Kind="Table"]}[Data],
    #"Added Custom" = Table.AddColumn(tb_Pers_Table, "CustomSurname", each Text.Combine(List.RemoveItems(Text.ToList([Surname]),Text.ToList(",.';"))))
in
    #"Added Custom";

shared CalendarNew2 = let
    Source = Excel.Workbook(File.Contents("C:\Users\cjack\Desktop\CalendarNew.xlsx"), null, true),
    CalendarNew2_Table = Source{[Item="CalendarNew2",Kind="Table"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(CalendarNew2_Table,{{"Date", type date}, {"Index", Int64.Type}, {"Year", Int64.Type}, {"Month Number", Int64.Type}, {"Month Name", type text}, {"YearID", Int64.Type}, {"Fin Year", Int64.Type}, {"FinWeekID", Int64.Type}, {"FinWeek of Quarter", Int64.Type}, {"FinWeek of Year", Int64.Type}, {"FinWeek Number", Int64.Type}, {"FinMonthID", Int64.Type}, {"FinMonthNo", Int64.Type}, {"FinYear/Month", Int64.Type}, {"FinWeekofMonth", Int64.Type}, {"FinMonthName", type text}, {"Day", type text}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Fin Year", Order.Descending}}),
    #"Added Conditional Column" = Table.AddColumn(#"Sorted Rows", "Custom", each if [Year] = 2018 then 1 else if [Year] = 2017 then 2 else null)
in
    #"Added Conditional Column";

shared VTS = let
    Source = Odbc.Query("dsn=BI_Reports", "SELECT T1.VMDEPO AS DEPOT,#(lf)T1.VMFRAN AS FRANCHISE,#(lf)T1.VMVINV AS ""MOVEMENT NO"",#(lf)T1.VMVHIP||T1.VMVHID||T1.VMCHAS as VIN,#(lf)T1.VMSORD AS SONO,#(lf)T1.VMCHAS,#(lf)DIGITS(T1.VMMANZ) as ZONE,#(lf)T1.VMPOCD as POSTCODE,#(lf)T1.VMSVST AS STATUS,#(lf)T1.VMDRVC AS DRIVER,#(lf)T1.VMFLET AS FLEET,#(lf)T1.VMDMLG AS MILES,#(lf)#(lf)CASE WHEN T1.VMDVDD > '0' THEN DATE(DIGITS(T1.VMDVCC)||DIGITS(T1.VMDVYY)||'-'||DIGITS(T1.VMDVMM)||'-'||DIGITS(T1.VMDVDD)) END AS ""DELIVERY DATE"",#(lf)CASE WHEN T1.VMDVDD > '0' THEN WEEK_ISO(DATE(DIGITS(T1.VMDVCC)||DIGITS(T1.VMDVYY)||'-'||DIGITS(T1.VMDVMM)||'-'||DIGITS(T1.VMDVDD))) END AS ""DELIVERY WEEK No."",#(lf)#(lf)T2.DENAME as DEALER_NAME,#(lf)T2.DESHAD AS TOWN,#(lf)#(lf)T3.MMDLR0 AS ""MISC ACCOUNT"",#(lf)#(lf)T4.MLCOLT AS ""MISC FROM TOWN"",#(lf)T4.MLPPCD AS ""MISC FROM POSTCODE"",#(lf)T4.MLDLVT AS ""MISC TO TOWN"",#(lf)T4.MLDPCD AS ""MISC TO POSTCODE""#(lf)#(lf)#(lf)FROM (VTDATA.VTVEHM AS T1#(lf)LEFT JOIN VTDATA.VTDELR AS T2 ON T1.VMFRAN = T2.DEFRAN and T1.VMDLR0 = T2.DEDLR0 AND T1.VMDPNT = T2.DEDPNT#(lf)LEFT JOIN VTDATA.VTMMVJ AS T3 ON T1.VMSORD = T3.MMJQNO#(lf)LEFT JOIN VTDATA.VTMMVL AS T4 ON T1.VMVINV = T4.MLVINV)#(lf)                                                                                  #(lf)                                                                                                                                                                      #(lf)WHERE T1.VMSVST IN ('DV','SM','IV')#(lf)#(lf)                                                                                                                                                                      #(lf)UNION ALL#(lf)#(lf)SELECT T1.VPDEPO AS DEPOT,#(lf)T1.VPFRAN AS FRANCHISE,#(lf)T1.VPVINV AS ""MOVEMENT NO"",#(lf)T1.VPCHAS,#(lf)T1.VPVHIP||T1.VPVHID||T1.VPCHAS as VIN,#(lf)T1.VPSORD AS SONO,#(lf)DIGITS(T1.VPMANZ) as ZONE,#(lf)T1.VPPOCD as POSTCODE,#(lf)T1.VPSVST AS STATUS,#(lf)T1.VPDRVC AS DRIVER,#(lf)T1.VPFLET AS FLEET,#(lf)T1.VPDMLG AS MILES,#(lf)#(lf)(DIGITS(T1.VPDVCC)||DIGITS(T1.VPDVYY)||'-'||DIGITS(T1.VPDVMM)||'-'||DIGITS(T1.VPDVDD)) AS ""DELIVERY DATE"",#(lf)WEEK_ISO(DATE(DIGITS(T1.VPDVCC)||DIGITS(T1.VPDVYY)||'-'||DIGITS(T1.VPDVMM)||'-'||DIGITS(T1.VPDVDD))) AS ""DELIVERY WEEK No."",#(lf)#(lf)T2.DENAME as DEALER_NAME,#(lf)T2.DESHAD AS TOWN,#(lf)#(lf)T3.MMDLR0 AS ""MISC ACCOUNT"",#(lf)#(lf)T4.MLCOLT AS ""MISC FROM TOWN"",#(lf)T4.MLPPCD AS ""MISC FROM POSTCODE"",#(lf)T4.MLDLVT AS ""MISC TO TOWN"",#(lf)T4.MLDPCD AS ""MISC TO POSTCODE""#(lf)#(lf)FROM (VTDATA.VTVEHP AS T1#(lf)LEFT JOIN VTDATA.VTDELR AS T2 ON T1.VPFRAN = T2.DEFRAN and T1.VPDLR0 = T2.DEDLR0 AND T1.VPDPNT = T2.DEDPNT#(lf)LEFT JOIN VTDATA.VTMMVJ AS T3 ON T1.VPSORD = T3.MMJQNO#(lf)LEFT JOIN VTDATA.VTMMVL AS T4 ON T1.VPVINV = T4.MLVINV)#(lf)")
in
    Source;

shared Dealers = let
    Source = Odbc.Query("dsn=BI_Reports", "select T1.""DEDLRC"" ""c1"" , T1.""DENAME"" ""c2"" , T1.""DETOWN"" ""c3"" , T1.""DECNTY"" ""c4"" , T1.""DEPOCD"" ""c5"" #(lf) from ""S068D48P"".""VTDATA"".""VTDELR"" T1")
in
    Source;

shared tb_hol_trans = let
    Source = Odbc.DataSource("dsn=Holiday Database2", [HierarchicalNavigation=true]),
    #"Y:\ecm_hol.mdb_Database" = Source{[Name="Y:\ecm_hol.mdb",Kind="Database"]}[Data],
    tb_hol_trans_Table = #"Y:\ecm_hol.mdb_Database"{[Name="tb_hol_trans",Kind="Table"]}[Data]
in
    tb_hol_trans_Table;

shared DriverPerformane = let
    Source = Excel.Workbook(File.Contents("C:\Users\cjack\Desktop\Fleet Analysis Report.xlsm"), null, true),
    Summary_Sheet = Source{[Item="Summary",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Summary_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Column1", type any}, {"Year - Week", type text}, {"Week Commence Date", type date}, {"Driver Name", type text}, {"Pay Agreement", type text}, {"Parking Location", type text}, {"Tracking System", type text}, {"Total Engine On Time hh:mm", type any}, {"Distance - Miles", Int64.Type}, {"Fuel - Gallons", type number}, {"M.P.G - including Drive, PTO and Idle", type number}, {"Average Speed - MPH", Int64.Type}, {"Average Brake Counter - hits/100 miles", Int64.Type}, {"PTO Time - % Engine On", type number}, {"Idling Time - % Engine On", type number}, {"Time Within Economy Band - % Engine On", type number}, {"Cruise Control Time - % Engine On", type number}, {"Vehicle Overspeed - % >56mph", type number}, {"Engine Load Time - %", type number}, {"ISO Brake Actions - Normal %", type number}, {"ISO Brake Actions - Medium %", type number}, {"ISO Brake Actions - Harsh %", type number}, {"ISO Harsh Acceleration", Int64.Type}, {"DYNA Adblue MPG", type any}, {"DYNA Adblue Consuption Ratio To Fuel", type any}, {"DYNA PTO Fuel Consumption - l/h", type any}, {"DYNA Automatic Time - %", type any}, {"DYNA Manual Time - %", type any}, {"Column29", type any}, {"Column30", type any}, {"Column31", type any}, {"Column32", type any}, {"Column33", type any}, {"Column34", type any}, {"Column35", type any}, {"Column36", type any}, {"Column37", type any}, {"Column38", type any}, {"Column39", type any}})
in
    #"Changed Type";

shared ecmDealers = let
    Source = Sql.Database("srvca10", "VDS_pROD"),
    dbo_ecmDealers = Source{[Schema="dbo",Item="ecmDealers"]}[Data]
in
    dbo_ecmDealers;

shared ecmDriver = let
    Source = Sql.Database("srvca10", "VDS_pROD"),
    dbo_ecmDriver = Source{[Schema="dbo",Item="ecmDriver"]}[Data]
in
    dbo_ecmDriver;

shared #"tb_Pers (2)" = let
    Source = Odbc.DataSource("dsn=Holiday Database2", [HierarchicalNavigation=true]),
    #"Y:\ecm_hol.mdb_Database" = Source{[Name="Y:\ecm_hol.mdb",Kind="Database"]}[Data],
    tb_Pers_Table = #"Y:\ecm_hol.mdb_Database"{[Name="tb_Pers",Kind="Table"]}[Data],
    #"Added Custom" = Table.AddColumn(tb_Pers_Table, "Custom", each Table.AddColumn(tb_Pers_Table, "CustomSurname", each Text.Combine(List.RemoveItems(Text.ToList([Surname]),Text.ToList(",.';")))) & " " & [CH_Name_Initials])
in
    #"Added Custom";

v-qiuyu-msft · ‎06-08-2018

Hi @Anonymous,

From the error message, the error occurs with the step #"Added Custom" below within table tb_Pers (2), right?

let
    Source = Odbc.DataSource("dsn=Holiday Database2", [HierarchicalNavigation=true]),
    #"Y:\ecm_hol.mdb_Database" = Source{[Name="Y:\ecm_hol.mdb",Kind="Database"]}[Data],
    tb_Pers_Table = #"Y:\ecm_hol.mdb_Database"{[Name="tb_Pers",Kind="Table"]}[Data],
    #"Added Custom" = Table.AddColumn(tb_Pers_Table, "Custom", each Table.AddColumn(tb_Pers_Table, "CustomSurname", each Text.Combine(List.RemoveItems(Text.ToList([Surname]),Text.ToList(",.';")))) & " " & [CH_Name_Initials])
in
    #"Added Custom";

I created a ODBC 64 bit DSN connect to SQL database, add a new custom column as yours in Query Editor, the new column returns fine without error occurs.

In your scenario, do you create ODBC 64 bit or 32 bit DSN? Is there any other action you performed after you add the custom column then issue occurs?

Please update the Power BI desktop to the latest version 2.59.5135.421 64-bit (June 2018) then test again.

Best Regards,
Qiuyun Yu

Anonymous · ‎06-08-2018

Hi @v-qiuyu-msft,

In the ODBC Data source administrator, it says I am using Client Access ODBC Driver (32-bit). However if I try and run the 64 bit installer it says that it is already installed on my machine.

So I am a bit confused as to what it is using.... No other steps where involved apart from what I mentioned in my original post.

Thanks

Anonymous · ‎06-08-2018

Hi, I reinstalled PowerBI but it is still throwing up issues.

Anonymous · ‎06-08-2018

It is using the Microsoft Access Driver 14.00.7180.5000

v-qiuyu-msft · ‎06-08-2018

Hi @Anonymous,

As it's Client Access ODBC Driver (32-bit), please create ODBC 32 bit DSN and test again with Power BI desktop 32 bit to see if the same issue occurs.

Best Regards,
Qiuyun Yu

Issue with Custom column in PowerQuery

Small multiples not fitting to scale

March 2025 Update - Error fetching data for this v...

Desktop Application Bug?? - PBI desktop applicatio...

Previously working Mac calender visual now gives m...

Azure Maps reference layer transparency behaves di...

A new Data Days event is coming soon!

Issue with Custom column in PowerQuery

Small multiples not fitting to scale

March 2025 Update - Error fetching data for this v...

Desktop Application Bug?? - PBI desktop applicatio...

Previously working Mac calender visual now gives m...

Azure Maps reference layer transparency behaves di...