I am sharing my detail with you and I have also emailed you at bennettray@microsoft.com
Transformation:
shared ImportMappingKey = [ account = { [ ExternalRecordKey = [IOMAccountMapping = Source[billingAddress][email]], SelectedFields = {"accountid"} ] }, pricelevel = { [ ExternalRecordKey = [IOMPriceList = "ContosoIOMPriceList"], SelectedFields = {"pricelevelid"} ] }, product = List.Distinct(List.Transform(Source[orderdetails], each [ ExternalRecordKey = [sku = _[sku]], SelectedFields = {"productid"} ])), uom = List.Distinct(List.Transform(Source[orderdetails], each [ ExternalRecordKey = [unit = _[unit]], SelectedFields = {"uomid"} ])) ]; shared TransformSourceData = let leadtime = 5, useWriteInProductEnabled = Source[useWriteInProductEnabled], isDualWriteEnabled = Source[isDualWriteEnabled], orderProducts = Source[orderdetails], billingAddress = Source[billingAddress], shippingAddress = Source[shippingAddress], account = IOM.MapRecord(IOM.MappingTables[account], [IOMAccountMapping = Source[billingAddress][email]]), pricelevel = IOM.MapRecord(IOM.MappingTables[pricelevel], [IOMPriceList = "ContosoIOMPriceList"]), orderheader = Record.FromTable ( Table.SelectRows ( Record.ToTable ( [ ordernumber = Text.From(Source[ordernumber]), name = ordernumber, #"customerid_account@odata.bind" = "/accounts(" & Text.From(account[accountid]) & ")", #"pricelevelid@odata.bind" = "/pricelevels(" & Text.From(pricelevel[pricelevelid]) & ")", billto_city = Record.FieldOrDefault(billingAddress, "billtocity"), billto_stateorprovince = Record.FieldOrDefault(billingAddress, "billtostateorprovince"), billto_country = Record.FieldOrDefault(billingAddress, "billtocountry"), billto_postalcode = Record.FieldOrDefault(billingAddress, "billtozip"), shipto_name = Record.FieldOrDefault(shippingAddress, "firstname") & " " & Record.FieldOrDefault(shippingAddress, "lastname"), shipto_contactname = shipto_name, shipto_line1 = Record.FieldOrDefault(shippingAddress, "street1"), shipto_line2 = Record.FieldOrDefault(shippingAddress, "street2"), shipto_city = Record.FieldOrDefault(shippingAddress, "shiptocity"), shipto_stateorprovince = Record.FieldOrDefault(shippingAddress, "shiptostateorprovince"), shipto_country = Record.FieldOrDefault(shippingAddress, "shiptocountry"), shipto_postalcode = Record.FieldOrDefault(shippingAddress, "shiptozip"), emailaddress = Record.FieldOrDefault(shippingAddress, "email") ] ), each [Value] <> null ) ), orderlines = List.Transform(orderProducts, each Record.FromTable ( Table.SelectRows ( Record.ToTable ( [ productdescription = if useWriteInProductEnabled = true then if Record.HasFields(IOM.MapRecord(IOM.MappingTables[product], [sku = Record.FieldOrDefault(_, "sku")]), "productid") then null else if isDualWriteEnabled = false then Text.From(Record.FieldOrDefault(_, "sku")) else null else null, ispriceoverridden = true, #"productid@odata.bind" = "/products(" & IOM.MapRecord(IOM.MappingTables[product], [sku = Record.FieldOrDefault(_, "sku")])[productid] & ")", #"uomid@odata.bind" = "/uoms(" & IOM.MapRecord(IOM.MappingTables[uom], [unit = Record.FieldOrDefault(_, "unit")])[uomid] & ")", quantity = [quantity], quantityshipped = Record.FieldOrDefault(_, "quantity_shipped"), priceperunit = Decimal.From(Record.FieldOrDefault(_, "base_price")), baseamount = Decimal.From(Record.FieldOrDefault(_, "base_total")), tax = Decimal.From(Record.FieldOrDefault(_, "total_tax")), shipto_name = Record.FieldOrDefault(orderheader, "shipto_name"), shipto_contactname = Record.FieldOrDefault(orderheader, "shiptocontact_name"), shipto_line1 = Record.FieldOrDefault(orderheader, "shipto_line1"), shipto_line2 = Record.FieldOrDefault(orderheader, "shipto_line2"), shipto_city = Record.FieldOrDefault(orderheader, "shipto_city"), shipto_stateorprovince = Record.FieldOrDefault(orderheader, "shipto_stateorprovince"), shipto_country = Record.FieldOrDefault(orderheader, "shipto_country"), shipto_postalcode = Record.FieldOrDefault(orderheader, "shipto_postalcode") ] ), each [Value] <> null ) ) ), salesorder = Record.AddField(orderheader, "order_details", orderlines) in Text.FromBinary(Json.FromValue(salesorder));
JSON:
[ { "useWriteInProductEnabled": true, "isDualWriteEnabled": false, "ordernumber": "ExternalContosoOrder025", "billingAddress": { "billtocity": "BELLEVUE", "billtostateorprovince": "WA", "billtocountry": "US", "billtozip": "98007", "email": "test@gmail.com" }, "shippingAddress": { "firstname": "Connie", "lastname": "Vrettos", "street1": "123 Coffee Street", "street2": "Suite 300", "shiptocity": "Redmond", "shiptostateorprovince": "WA", "shiptocountry": "US", "shiptozip": "98052", "email": "test@gmail.com" }, "orderdetails": [ { "sku": "883988211855", "unit": "each", "quantity": 11, "base_price": 12.25, "base_total": 134.75, "total_tax": 20.21 }, { "sku": "cleankit443567", "unit": "each", "quantity": 21, "base_price": 20.00, "base_total": 420.00, "total_tax": 63.00 } ] } ]
