Transforming json with power query (mix of list and Text in a single column)

[sankallada]

Hi anvg/all,


I'm facing another issue in one of my feeds. ThI'm facing another issue in one of my feeds. This is also due to mix of list and text value in a single column. Please note that this is related to http://www.mrexcel.com/forum/power-...er-query-mix-list-record-single-column-2.html.

I tried to modify the code which have given to handle this situation too. But getting an error while trying to expand:


let
Source = Json.Document(Web.Contents("http://www.acmee.com/feeds/properties.all.json")),
#"Converted to Table" = Record.ToTable(Source),
Value = #"Converted to Table"{1}[Value],
#"Converted to Table1" = Table.FromList(Value, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table1", "Column1", {"name", "propCode", "secondaryImageUrl"}, {"name", "propCode", "secondaryImageUrl"}),
fieldForRec = Table.AddColumn(#"Expanded Column1","Rec",each if Value.Is([secondaryImageUrl], type text) then [secondaryImageUrl] else null,type text),
fieldForList = Table.AddColumn(fieldForRec, "List",each if Value.Is([secondaryImageUrl], type list) then [secondaryImageUrl] else null,type list)
in
fieldForList




Scenario:


Most of the cases the attribute secondaryImageUrl gives an array of images (list) but in some cases it it gives a string value of type text. With the above script I'm ab
le to split list and text values in two columns. But while trying to expand the list column i'm getting an exception :


Stack Trace:
Microsoft.Mashup.Host.Document.JavaScriptException: DOM Exception: NOT_FOUND_ERR (8)
at local://pq.microsoft.com/ts/widgets/VirtualizedList.js:line 264


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.UnexpectedExceptionHandler.<>c__DisplayClass1.b__0()
at Microsoft.Mashup.Client.ClientShared.UnexpectedExceptionHandler.HandleException(Exception e)
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.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)
at Microsoft.Mashup.Client.ClientShared.JsExportWrapper.<>c__DisplayClass2.b__0()
at Microsoft.Mashup.Client.ClientShared.JsErrorHandler.WrapInvokeHost(Func`1 invokeHost)
at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
at System.Windows.Forms.Application.ThreadContext.LocalModalMessageLoop(Form form)
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.Excel.QueriesEditorWindowManager.ShowModalQueriesEditorWindow(IWin32Window ownerWindow, IWorkbookIdentity workbookIdentity, QueriesEditorParameters queriesEditorParameters, Queries queries)
at Microsoft.Mashup.Client.Excel.DialogManager.TryShowModalEditor(IWin32Window ownerWindow, IWorkbook workbook, Query query, Boolean isNewQuery, Boolean navigatorExpanded)
at Microsoft.Mashup.Client.Excel.ExcelQueryServices.<>c__DisplayClass1d.b__1c(IWorkbook workbook)
at Microsoft.Mashup.Client.Excel.ExcelQueryServices.TryInvokeOnWorkbook(Action`1 action)
at Microsoft.Mashup.Client.Excel.QueriesTaskPaneControl.<>c__DisplayClass6.b__5()
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)




Please find a sample JSON feed to to explain the issue:






{
"@timestamp": "06-12-2015 22:20:07 PM",
"property": [{
"name": "name1",
"propCode": "prop1",
"secondaryImageUrl": ["/abc/cde/Property-Interior-1.jpg",
"/abc/fgh/Property-Exterior-Hotel-2.jpeg"]
},
{
"name": "name2",
"propCode": "prop2",
"secondaryImageUrl": "/abc/ijk/Property-Interior-1.jpg"
},
{
"name": "name3",
"propCode": "prop3",
"secondaryImageUrl": ["/abc/lmn/Property-Interior-1.jpg",
"/abc/opq/Property-Exterior-Hotel-2.jpeg",
"/abc/rst/Gaming-Table-Games-3.jpg"]
}]
}




Thanks and Regards,

 

[anvg]

Hi, sankallada
Please, try with

let
    source = Json.Document(File.Contents("C:\arcgis\test.json")),
    toTable = Table.FromRecords({source}),
    expandedListCol = Table.ExpandListColumn(toTable, "property"),
    expandedRecCol = Table.ExpandRecordColumn(expandedListCol, "property", {"name", "propCode", "secondaryImageUrl"}),
    transformed = Table.TransformColumns(expandedRecCol, { {"secondaryImageUrl", each if Value.Is(_, type list) then _ else {_}, type list} }),
    return = Table.ExpandListColumn(transformed, "secondaryImageUrl")
in
    return

Regards,