Forum Discussion
Solved
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. ...
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