Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
@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
@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
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
@smpa01, I got it to work. This helped me figure out how to do the formatting needed. Thank you for your help.
Damian
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.