Power Query - Json

[stanl]

This was fun. I anticipate needing to transform Json data generated by API calls into Excel. As Power Query can handle Json data I looked into using M Code rather than macros or other coding. The problem is when you generate M Code by manually querying Json data the columns are hard-coded. Also, Json is differentiated from other text input [i.e. .csv] as Power Query handles the columns as Records.


The script below has M Code in a UDF and {hopefully} generates dynamic column names. I have attached 2 Json files to test with. They are similar but the stan.json has an additional element in the json.

Code (WINBATCH) Select Expand



;Winbatch 2021b - Testing Excel Power Query for Json Data
;Requires Excel 2016 or Higher or Power Query added to Excel 2013 or earlier
;Script uses .json file as source
;M code is generic for Json files with lead element.
;Have not tested with nested Json arrays
;Stan Littlefield May 21, 2021
IntControl(73,1,0,0,0)
gosub udfs
types="Json Files|*.json|"
cFile=AskFilename("Select Json File",dirscript(), types, "", 101)
If ! FileExist(cFile) Then Terminate(@TRUE,"Cannot Continue, Source file ",cFile:" not found")
cXLS = Dirscript(): FileRoot(cFile):".xlsx"
qry =  FileRoot(cFile):"_Json"
If FileExist(cXLS) Then FileDelete(cXLS)
cn = "Query - ":qry
mcode = getmcode()
BoxOpen("Please Wait","Creating Excel Power Query for Json Data")
toExcel()
BoxShut()


If fileexist(cXLS)
   Message("File Created",cXLS)
Else
   Message("Failed To Create File",cXLS)
Endif


Exit
;======================================================================================================
:WBERRORHANDLER
Terminate(@TRUE,"Error Encountered",geterror())
;======================================================================================================
:CANCEL
Display(2,"Operation Canceled","Goodbye...")
Exit
:udfs
#DefineSubRoutine geterror()
   wberroradditionalinfo = wberrorarray[6]
   lasterr = wberrorarray[0]
   handlerline = wberrorarray[1]
   textstring = wberrorarray[5]
   linenumber = wberrorarray[8]
   errmsg = "Error: ":lasterr:@LF:textstring:@LF:"Line (":linenumber:")":@LF:wberroradditionalinfo
   Return(errmsg)
#EndSubRoutine


#DefineSubRoutine getmcode()
code = $"
let
    Source = Json.Document(File.Contents("|file|")),
    recs = Record.ToTable(Source),
    ##"Expanded Value" = Table.ExpandListColumn(recs, "Value"),
    ##"Expanded Value1" = Table.ExpandRecordColumn(##"Expanded Value", "Value",
    Record.FieldNames(##"Expanded Value"[Value]{0}),
    Record.FieldNames(##"Expanded Value"[Value]{0}))
in
    ##"Expanded Value1"
$"
code = StrReplace(code,"|file|",cFile)
Return(code)
#EndSubRoutine


#DefineSubRoutine toExcel()
oXL = CreateObject("Excel.Application")
If oXL == 0 Then Terminate(@TRUE,"Script Will Terminate","Cannot Start Excel Instance")
oXL.Visible          = @TRUE  ; change this to @FALSE to run hidden
oXL.ScreenUpdating   = @TRUE  ; if running hidden, change this to @FALSE
oXL.UserControl      = @TRUE
oXL.DisplayAlerts    = @FALSE
oXL.WorkBooks.Add()
BoxShut()
oWS = oXL.ActiveWorkBook.Worksheets(1)
oWS.Activate()
oWS.Name = qry
BoxOpen("Please Wait","Creating Query/Saving Workbook/Then Close Excel")
oXL.ActiveWorkBook.Queries.Add(::Name=qry,Formula=mcode,Description="Json Query")
cSource = "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=%qry%;Extended Properties=''"
qt = oWS.ListObjects.Add(::SourceType=0,Source=cSource,Destination=oWS.Range("$A$1")).QueryTable
qt.CommandText = "Select * FROM [%qry%]"
qt.Refresh()
;may take some time but should save
oXL.ActiveWorkbook.SaveAs(cXLS)
oXL.ActiveWorkbook.Close()
oXL.Quit()
oWS=0
oXL=0
Return(1)
#EndSubRoutine




Return