Forum Discussion
Create JSON script from table for rest api post
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
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
6 Replies
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"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
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
