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