Creating a table in Tabular Object Model

[HarrisonOVT]

Hi,

I am currently trying to develop an external tool and implement a function which creates and adds a data table to the model.  When I run the function, I recieve the following error:

Feedback Type:
Frown (Error)

Error Message:
Object reference not set to an instance of an object.

Stack Trace:
System.NullReferenceException
   at Microsoft.PowerBI.Client.Windows.ExplorationClientHost.<EndModelChangesTask>d__76.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Microsoft.PowerBI.Client.Windows.Services.EventAggregationService.<>c__DisplayClass22_0`2.<<PublishAndMarshalExceptions>b__1>d.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Microsoft.PowerBI.Client.Windows.IExceptionHandlerExtensions.<HandleAwaitableAsyncExceptions>d__1.MoveNext()

Stack Trace Message:
Object reference not set to an instance of an object.

Invocation Stack Trace:
   at Microsoft.Mashup.Host.Document.ExceptionExtensions.GetCurrentInvocationStackTrace()
   at Microsoft.Mashup.Client.UI.Shared.StackTraceInfo..ctor(String exceptionStackTrace, String invocationStackTrace, String exceptionMessage)
   at Microsoft.PowerBI.Client.Windows.Telemetry.PowerBIUserFeedbackServices.GetStackTraceInfo(Exception e)
   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__DisplayClass15_0.<HandleException>b__0()
   at Microsoft.Mashup.Client.UI.Shared.UnexpectedExceptionHandler.HandleException(Exception e)
   at Microsoft.PowerBI.Client.Windows.IExceptionHandlerExtensions.<HandleAwaitableAsyncExceptions>d__1.MoveNext()
   at System.Runtime.CompilerServices.AsyncTaskMethodBuilder.Start[TStateMachine](TStateMachine& stateMachine)
   at Microsoft.PowerBI.Client.Windows.IExceptionHandlerExtensions.HandleAwaitableAsyncExceptions(IExceptionHandler exceptionHandler, Func`1 asyncFunc)
   at Microsoft.PowerBI.Client.Windows.IExceptionHandlerExtensions.<HandleAsyncExceptions>d__0.MoveNext()
   at System.Runtime.CompilerServices.AsyncVoidMethodBuilder.Start[TStateMachine](TStateMachine& stateMachine)
   at Microsoft.PowerBI.Client.Windows.IExceptionHandlerExtensions.HandleAsyncExceptions(IExceptionHandler exceptionHandler, Func`1 asyncFunc)
   at Microsoft.Mashup.Host.Document.ExceptionHandlerExtensions.HandleExceptions(IExceptionHandler exceptionHandler, Action action)
   at Microsoft.PowerBI.Client.Windows.Services.EventAggregationService.Publish[TEvent,TEventArg](TEventArg eventArgs)
   at Microsoft.PowerBI.Client.Windows.AnalysisServices.AnalysisServicesDatabaseLocal.<OnExternalXmlaChange>d__73.MoveNext()
   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.Runtime.CompilerServices.AsyncMethodBuilderCore.MoveNextRunner.Run()
   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.WindowManager.ShowModal[T](T dialog, Func`1 showModalFunction)
   at Microsoft.PowerBI.Client.Program.<>c__DisplayClass4_0.<Main>b__1()
   at Microsoft.PowerBI.Client.Windows.IExceptionHandlerExtensions.<>c__DisplayClass3_0.<HandleExceptionsWithNestedTasks>b__0()
   at Microsoft.Mashup.Host.Document.ExceptionHandlerExtensions.HandleExceptions(IExceptionHandler exceptionHandler, Action action)
   at Microsoft.PowerBI.Client.Program.Main(String[] args)


PowerBINonFatalError:
{"AppName":"PBIDesktop","AppVersion":"2.96.1061.0","ModuleName":"Microsoft.PowerBI.Client.Windows.dll","Component":"Microsoft.PowerBI.Client.Windows.ExplorationClientHost+<EndModelChangesTask>d__76","Error":"System.NullReferenceException","MethodDef":"MoveNext","ErrorOffset":"25"}

Snapshot Trace Logs:
C:\Users\harrison\AppData\Local\Microsoft\Power BI Desktop\FrownSnapShot0e43f625-555b-4fca-9266-ccb4cc7b497e.zip

Model Default Mode:
Import

Model Version:
PowerBI_V3

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

Enabled Preview Features:
PBI_JsonTableInference
PBI_NewWebTableInference
PBI_ImportTextByExample
PBI_ExcelTableInference
PBI_rdlNativeVisual

Disabled Preview Features:
PBI_shapeMapVisualEnabled
PBI_SpanishLinguisticsEnabled
PBI_qnaLiveConnect
PBI_azureMapVisual
PBI_dataPointLassoSelect
PBI_compositeModelsOverAS
PBI_dynamicParameters
PBI_enhancedTooltips
PBI_enableWebView2

Disabled DirectQuery Options:
TreatHanaAsRelationalSource

Cloud:
GlobalCloud

PowerBIUserFeedbackServices_IsReported:
True

Formulas:


section Section1;

shared financials = let
 Source = Excel.Workbook(File.Contents("C:\Program Files\Microsoft Power BI Desktop\bin\SampleData\Financial Sample.xlsx"), null, true),
    financials_Table = Source{[Item="financials",Kind="Table"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(financials_Table,{{"Segment", type text}, {"Country", type text}, {"Product", type text}, {"Discount Band", type text}, {"Units Sold", type number}, {"Manufacturing Price", Int64.Type}, {"Sale Price", Int64.Type}, {"Gross Sales", type number}, {"Discounts", type number}, {" Sales", type number}, {"COGS", type number}, {"Profit", type number}, {"Date", type date}, {"Month Number", Int64.Type}, {"Month Name", type text}, {"Year", Int64.Type}})
in
 #"Changed Type";

The data table shows up on the model populated, however the option to apply or discard changes appear everytime. Regardless of the options I select, the data table will then be removed. 

The function code I came up with after following a few tutorials: 

        public static void CreateTable()
        {
            Console.WriteLine("Enter a name for the Table: ");
            string tableName = Console.ReadLine();
            Table tempTable = new Table()
            {
                Name = tableName,
                Partitions =
                {
                    new Partition()
                    {
                        Name = "Temp",
                        Mode = ModeType.Import,
                        Source = new MPartitionSource()
                        {
                            Expression = @"let
 Source = Excel.Workbook(File.Contents(""C:\\Users\\harrison.yee\\Documents\\Excel\\Financial Sample 2.xlsx""), null, true),
    financials_Table = Source{[Item=""financials"",Kind=""Table""]}[Data],
    #""Changed Type"" = Table.TransformColumnTypes(financials_Table,{{""Segment"", type text}, {""Country"", type text}, {""Product"", type text}, {""Discount Band"", type text}, {""Units Sold"", type number}, {""Manufacturing Price"", Int64.Type}, {""Sale Price"", Int64.Type}, {""Gross Sales"", type number}, {""Discounts"", type number}, {"" Sales"", type number}, {""COGS"", type number}, {""Profit"", type number}, {""Date"", type date}, {""Month Number"", Int64.Type}, {""Month Name"", type text}, {""Year"", Int64.Type}})
in
 #""Changed Type"""
                        }
                    }
                },
                Columns =
                {
                    new DataColumn()
                    {
                        Name = "TestSegment",  
                        DataType = DataType.String,
                        SourceColumn = "Segment"
                    },
                    new DataColumn()
                    {
                        Name = "TestCountry",
                        DataType = DataType.String,
                        SourceColumn = "Country"
                    }
                }
            };
            model.Tables.Add(tempTable);
            model.SaveChanges();
            model.RequestRefresh(RefreshType.Full);                                
            dataBase.Update(Microsoft.AnalysisServices.UpdateOptions.ExpandFull);          
            model.SaveChanges();
        }

 model and dataBase is defined as follows:

model = server.Databases[0].Model;
dataBase = server.Databases.GetByName(model.Database.Name);

Any help or tip is greatly appreciated.

Thank you,

Harrison 

[v-rzhou-msft]

Hi @HarrisonOVT ,

Do you want to create a table in Tabular Editor and then load the table into Power BI Desktop? The table you created in Tabular is calculated table. It is the same as dax table in Power BI Desktop. Tabular Editor is a third party tool, if you want to use it, make sure you have a backup. 

I suggest you to copy the code in Tabular and then try it in Power BI Desktop. To make sure your code is correct.

Here I have a test. It works well.

Result:

Here is a video about how to create a calcualte table in Tabular.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[HarrisonOVT]

Hi @v-rzhou-msft ,

Thanks for getting back! I am not trying to create a calculated table. I want to create a new table using data from outside sources, such as excel or a database. As you can see from this example, under partitions the mode type is import and the source is from a local excel sheet, whose data does not exist on the Power BI Desktop model. I understand a calculated table is generating a table using existing data from another table in the model, but I am trying to add a new table with new data into the model. 

Similarly to what this documentaion is trying to do, under the heading "Creating a Real-World Dataset from Scratch" : https://www.powerbidevcamp.net/articles/programming-datasets-with-TOM/ 

Currently, I am trying to develop my own external tool program, and not use existing tools. This function is a step towards my goal, but I am not sure why I run into the errors shown above. 

Thank you,

Harrison

[dotykier · Accepted solution]

Hi Harrison,
Unfortunately, external tools can't use all possible data modelling options of the TOM, as most are unsupported. Here is a list of the operations that do work:

https://docs.microsoft.com/en-us/power-bi/transform-model/desktop-external-tools#supported-write-ope...

If you are creating an external tool that allows for operations other than those listed there, please make sure to provide a disclaimer to your users, that the tool is about to make unsupported changes that could potentially break the pbix file.