Create JSON script from table for rest api post

[damianmarquith]

All,

I am attempting to create a complex JSON script within Power Query to be used as a rest API post from a table.  The format required must conform to the record, list, and array requirements.

The JSON format is as follows:

{
"accountGroup": {
"code": "1234",
"accounts": [
{
"accountCode": "ABCD"
}
]
},
"identifiers": [
{
"type": "accountNumber",
"value": "23414234565"
},
{
"type": "customerNumber",
"value": "872958729"
}
],
"shipmentStops": [
{
"stopType": "ORIGIN",
"appointmentWindow": {
"startDateTime": "2021-01-22T09:00:00"
"endDateTime": "2021-01-22T10:00:00"
},
"location": {
"address": {
"postalCode": "48290",
"addressLines": [
"1234 Pine Lane"
],
"city": "Atlanta",
"state": "GA",
"country": "US"
},
"contact": {
"companyName": "ACME Logistics"
}
}
},
{
"stopType": "DESTINATION",
"appointmentWindow": {
"startDateTime": "2021-01-26T11:00:00",
"endDateTime": "2021-01-26T17:00:00"
},
"location": {
"address": {
"postalCode": "55555",
"addressLines": [
"876 Oak Blvd."
],
"city": "Daytona Beach",
"state": "FL",
"country": "US"
},
"contact": {
"companyName": "Daytona Beach"
}
}
}
],
"apiConfiguration": {
"fallBackToDefaultAccountGroup": 1
}
}

An example of the table that I am using : 

code

accountCode

accountNumber

customerNumber

stopType

startDateTime

endDateTime

postalCode

addressLine1

addressLine2

addressLine3

city

state

country

companyName

stopType

startDateTime

endDateTime

postalCode

addressLine1

addressLine2

addressLine3

city

state

country

companyName

1234

ABCD

23414234565

872958729

ORIGIN

2021-01-22T09:00:00

2021-01-22T10:00:00

48290

1234 Pine Lane

Atlanta

GA

US

ACME Logistics

DESTINATION

2021-01-26T11:00:00

2021-01-26T17:00:00

55555

876 Oak Blvd.

Daytona Beach

FL

US

Daytona Beach INC

2398

DTGH

23906902812

874357041

ORIGIN

2021-01-22T09:00:00

2021-01-22T10:00:00

48290

1234 Pine Lane

Atlanta

GA

US

ACME Logistics

DESTINATION

2021-01-26T11:00:00

2021-01-26T17:00:00

44444

8080 Mulbury Lane

Tampa

FL

US

Tampa INC

I have attempted to reverse engineer, by parsing the JSON script into a readable table ... and then create a second power query script to undo the parsing steps.  I have a few steps completed by using the Table.CombineColumnsToRecord to reverse the Table.ExpandRecordColumn, however I cannot seem to find a way to reverse the Table.ExpandListColumn.  It seems there is no Table.CombineColumnsToList function.  

Any hints or suggestions appreciated.

Thanks in advance,

Damian

[smpa01]

@damianmarquithcan you try this

let
    Source = Web.BrowserContents("https://community.powerbi.com/t5/Power-Query/Create-JSON-script-from-table-for-rest-api-post/m-p/1621228#M49729"),
    #"Extracted Table From Html" = Html.Table(Source, {{"Column1", "TABLE:nth-child(9) > * > TR > :nth-child(1)"}, {"Column2", "TABLE:nth-child(9) > * > TR > :nth-child(2)"}, {"Column3", "TABLE:nth-child(9) > * > TR > :nth-child(3)"}, {"Column4", "TABLE:nth-child(9) > * > TR > :nth-child(4)"}, {"Column5", "TABLE:nth-child(9) > * > TR > :nth-child(5)"}, {"Column6", "TABLE:nth-child(9) > * > TR > :nth-child(6)"}, {"Column7", "TABLE:nth-child(9) > * > TR > :nth-child(7)"}, {"Column8", "TABLE:nth-child(9) > * > TR > :nth-child(8)"}, {"Column9", "TABLE:nth-child(9) > * > TR > :nth-child(9)"}, {"Column10", "TABLE:nth-child(9) > * > TR > :nth-child(10)"}, {"Column11", "TABLE:nth-child(9) > * > TR > :nth-child(11)"}, {"Column12", "TABLE:nth-child(9) > * > TR > :nth-child(12)"}, {"Column13", "TABLE:nth-child(9) > * > TR > :nth-child(13)"}, {"Column14", "TABLE:nth-child(9) > * > TR > :nth-child(14)"}, {"Column15", "TABLE:nth-child(9) > * > TR > :nth-child(15)"}, {"Column16", "TABLE:nth-child(9) > * > TR > :nth-child(16)"}, {"Column17", "TABLE:nth-child(9) > * > TR > :nth-child(17)"}, {"Column18", "TABLE:nth-child(9) > * > TR > :nth-child(18)"}, {"Column19", "TABLE:nth-child(9) > * > TR > :nth-child(19)"}, {"Column20", "TABLE:nth-child(9) > * > TR > :nth-child(20)"}, {"Column21", "TABLE:nth-child(9) > * > TR > :nth-child(21)"}, {"Column22", "TABLE:nth-child(9) > * > TR > :nth-child(22)"}, {"Column23", "TABLE:nth-child(9) > * > TR > :nth-child(23)"}, {"Column24", "TABLE:nth-child(9) > * > TR > :nth-child(24)"}, {"Column25", "TABLE:nth-child(9) > * > TR > :nth-child(25)"}, {"Column26", "TABLE:nth-child(9) > * > TR > :nth-child(26)"}}, [RowSelector="TABLE:nth-child(9) > * > TR"]),
    #"Promoted Headers" = Table.PromoteHeaders(#"Extracted Table From Html", [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"code", type text}, {"accountCode", type text}, {"accountNumber", type text}, {"customerNumber", type text}, {"stopType", type text}, {"startDateTime", type text}, {"endDateTime", type text}, {"postalCode", type text}, {"addressLine1", type text}, {"addressLine2", type text}, {"addressLine3", type text}, {"city", type text}, {"state", type text}, {"country", type text}, {"companyName", type text}, {"stopType_1", type text}, {"startDateTime_2", type text}, {"endDateTime_3", type text}, {"postalCode_4", type text}, {"addressLine1_5", type text}, {"addressLine2_6", type text}, {"addressLine3_7", type text}, {"city_8", type text}, {"state_9", type text}, {"country_10", type text}, {"companyName_11", type text}}),
    Custom1 = Table.ToRecords(#"Changed Type"),
    #"Converted to Table" = Table.FromList(Custom1, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Added Custom" = Table.AddColumn(#"Converted to Table", "Custom", each Json.FromValue([Column1]))
in
    #"Added Custom"

[damianmarquith]

@smpa01 , Thanks for the quick reply.  However, your solution only converts the table into a series of records: 

[{"Column1":

   "code":"1234",

   "accountCode":"ABCD",

   "accountNumber":"23414234565",

   "customerNumber"":872958729" ,

--{etc.}--

}]

I need to have the JSON formatted as above :

{
   "accountGroup": {
         "code": "1234",
         "accounts": [
               {
                   "accountCode": "ABCD"
               }
             ]
           },
    "identifiers": [
          {
                "type": "accountNumber",
                "value": "23414234565"
          },

--{etc}--

}

Again, appreciate the suggestion .. but looking at how to extract the table including some of the records as list and arrays where appropriate.

Damian

[smpa01 · Accepted solution]

@damianmarquith  since the formatting is custom, you need to custommize it as per your need

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("HYqxDcAwCAR3oXYBGGwok1hKBkiH2H+NEDf3+v+LAOIu0OA4r1VRhaSgQ6vZZNcfkC3qc6txvfezTcfhyEa8Tek6UQgyPw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [code = _t, accountCode = _t, accountNumber = _t, customerNumber = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"code", Int64.Type}, {"accountCode", type text}, {"accountNumber", Int64.Type}, {"customerNumber", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "accountCode.1", each {[accountCode]}),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "identifiers", each {[accountNumber],[customerNumber]}),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom1",{"code", "accountCode.1", "identifiers"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Other Columns",{{"accountCode.1", "accountCode"}}),
    Custom1 = Table.ToRecords(#"Renamed Columns"),
    #"Converted to Table" = Table.FromList(Custom1, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Added Custom2" = Table.AddColumn(#"Converted to Table", "Custom", each Json.FromValue([Column1])),
    #"Filtered Hidden Files1" = Table.SelectRows(#"Added Custom2", each [Attributes]?[Hidden]? <> true),
    #"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File (2)", each #"Transform File (2)"([Custom])),
    #"Removed Other Columns1" = Table.SelectColumns(#"Invoke Custom Function1", {"Transform File (2)"}),
    #"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File (2)", Table.ColumnNames(#"Transform File (2)"(#"Sample File (2)"))),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type1","]","",Replacer.ReplaceText,{"Column2"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","[","",Replacer.ReplaceText,{"Column2"}),
    #"Added Prefix" = Table.TransformColumns(#"Replaced Value1", {{"Column2", each """accounts"": [{" & _, type text}}),
    #"Added Suffix" = Table.TransformColumns(#"Added Prefix", {{"Column2", each _ & "}]},", type text}}),
    #"Replaced Value2" = Table.ReplaceValue(#"Added Suffix","identifiers:[","""identifiers"": [""type"": ""accountNumber"",""value"": """,Replacer.ReplaceText,{"Column3"}),
    #"Added Suffix1" = Table.TransformColumns(#"Replaced Value2", {{"Column3", each _ & """},", type text}}),
    #"Added Prefix1" = Table.TransformColumns(#"Added Suffix1", {{"Column4", each "{""type"": ""customerNumber"",""value"":""" & _, type text}}),
    #"Replaced Value3" = Table.ReplaceValue(#"Added Prefix1","]}","""}]",Replacer.ReplaceText,{"Column4"}),
    #"Added Custom3" = Table.AddColumn(#"Replaced Value3", "Custom", each [Column1]&","&[Column2]&","&[Column3]&","&[Column4])
in
    #"Added Custom3"

The above is an example and I have done as far as  customerNumber to give yuo the idea

[damianmarquith]

@smpa01 ,  Again - thank you for the help.  I believe you may have a custom function embeded in the power query.  I am getting an error at #"Invoice Custom Function1"

with the error,  the name "Transform File (2)" wasn't recognized.  Is there a separate custom function that you are referring to in the query?  

Many thanks. 

Damian

[smpa01]

when you come up to "Added Custom 2" expand Custom, PQWRY will create the necessary steps up to #"Changed Type1".

Then you can copy paste the rest in advanced editor

[damianmarquith]

@smpa01,  I got it to work.  This helped me figure out how to do the formatting needed.  Thank you for your help.

Damian