Power Platform Community Forum Thread Details

Hello!
I'm currently struggling on building a power automate flow that will retrieve me a table from excel and build a Select that will be later used to bulk post it on a Sharepoint List, I'm taking some guidance on This Post from @grantjenkins that helped me to get the headers from the table, but now i need to build the Select to get it like this post so I can then do the bulk patch.

I'm trying to build it using the same logic than the first post, but my issue is on the Select component. Looks like this:

 From: xpath(item(), '/*[not(name()="ItemInternalId")]')
 Map:
 {
 "Key": xpath(item(), 'name()'),
 "Value": xpath(item(), 'text()')
 }

on the result the input looks like this:

{
 "from": [
 {
 "$content-type": "application/xml;charset=utf-8",
 "$content": "PERBVEVfeDAwMjBfRU5URVJFRD4yMDIyLTAyLTAxVDAwOjAwOjAwLjAwMFo8L0RBVEVfeDAwMjBfRU5URVJFRD4="
 },
 {
 "$content-type": "application/xml;charset=utf-8",
 "$content": "PEdIU1BfeDAwMjBfUF94MDAyRl9OPjEtMTAwMDkxPC9HSFNQX3gwMDIwX1BfeDAwMkZfTj4="
 },
 {
 "$content-type": "application/xml;charset=utf-8",
 "$content": "PFNVUFBMSUVSPk1FTDwvU1VQUExJRVI+"
 },
 {
 "$content-type": "application/xml;charset=utf-8",
 "$content": "PE1GRz5NZWxleGlzPC9NRkc+"
 },
 {
 "$content-type": "application/xml;charset=utf-8",
 "$content": "PEN1cnJlbnRfeDAwMjBfTGVhZF94MDAyMF9UaW1lX3gwMDIwX194MDAyOF9XZWVrc194MDAyOV8+PC9DdXJyZW50X3gwMDIwX0xlYWRfeDAwMjBfVGltZV94MDAyMF9feDAwMjhfV2Vla3NfeDAwMjlfPg=="
 },
 {
 "$content-type": "application/xml;charset=utf-8",
 "$content": "PFByZXZpb3VzX3gwMDIwX194MDAyMF9MX3gwMDJGX1Q+PC9QcmV2aW91c194MDAyMF9feDAwMjBfTF94MDAyRl9UPg=="
 },
 {
 "$content-type": "application/xml;charset=utf-8",
 "$content": "PE1QTj48L01QTj4="
 },
 {
 "$content-type": "application/xml;charset=utf-8",
 "$content": "PE1QTjI+TUxYODAxMDRLTFEtREFHLTAwMC1SRTwvTVBOMj4="
 },
 {
 "$content-type": "application/xml;charset=utf-8",
 "$content": "PFdlZWtseV94MDAyMF9kZW1hbmQ+PC9XZWVrbHlfeDAwMjBfZGVtYW5kPg=="
 },
 {
 "$content-type": "application/xml;charset=utf-8",
 "$content": "PEVzdF94MDAyMF9MaW5lX3gwMDIwX0Rvd25feDAwMjBfQmFzZWRfeDAwMjBfb25feDAwMjBfR0hTUF94MDAyMF9NUlA+Ti9BPC9Fc3RfeDAwMjBfTGluZV94MDAyMF9Eb3duX3gwMDIwX0Jhc2VkX3gwMDIwX29uX3gwMDIwX0dIU1BfeDAwMjBfTVJQPg=="
 },
 {
 "$content-type": "application/xml;charset=utf-8",
 "$content": "PE5leHRfeDAwMjBfRGVsaXZlcnk+PC9OZXh0X3gwMDIwX0RlbGl2ZXJ5Pg=="
 },
 {
 "$content-type": "application/xml;charset=utf-8",
 "$content": "PExhc3RfeDAwMjBfU2hpcF94MDAyMF9kYXRlX3gwMDIwX3RvX3gwMDIwX0dIU1A+NDQ2ODA8L0xhc3RfeDAwMjBfU2hpcF94MDAyMF9kYXRlX3gwMDIwX3RvX3gwMDIwX0dIU1A+"
 },
 {
 "$content-type": "application/xml;charset=utf-8",
 "$content": "PEN1c3RvbWVyPjEyLjUgTEQgdXBkYXRlZCAtIGluY2x1ZGVzIDI1NWsgYXQgRnV0dXJlIHdlIHdpbGwgaGF2ZSB0byB0YWtlIGFkZGl0aW9uYWwgMjVrIGluIHRyYW5zaXQgdG8gRnV0dXJlLiAgUGxlYXNlIHNoaXAgbm9ybWFsIGFnYWluc3Qgb3VyIHJlbGVhc2VzIC0gRU9MIGZvciB0aGUgY3VycmVudCBNUE4sIE1lbGV4aXMgY29uZmlybWVkIHRoYXQgdGhleSBjYW4gc3VwcG9ydCB0aGUgc2hpcCBwbGFuIHByb3ZpZGVkIGluIHRoZSBsYXN0IG1lZXRpbmcgIC0gQSBuZXcgYmxhbmtldCBQTyB3aWxsIGJlIGlzc3VlZCBmb3IgdGhlIG5ldyBwYXJ0IG51bWJlci48L0N1c3RvbWVyPg=="
 },
 {
 "$content-type": "application/xml;charset=utf-8",
 "$content": "PENvbW1lbnRzPjwvQ29tbWVudHM+"
 },
 {
 "$content-type": "application/xml;charset=utf-8",
 "$content": "PEZ1dHVyZV94MDAyN19zX3gwMDIwX3N0YXR1c194MDAyMF91cGRhdGVzPjI1NUsgYWxsb2NhdGVkIHRvIEdIU1A7IE5leHQgZXN0aW1hdGVkIGRvY2sgZGF0ZSB0byBGdXR1cmUgaXMgMTBLIG9uIDEyLzE4LCAxMEsgb24gMTIvMjUgYW5kIDVLIG9uIDEvMS4gVGhlc2UgYXJlIGFsbCB0aGUgcmVtYWluaW5nIG9wZW4gb3JkZXJzLiBBbmQgc2luY2UgdGhlc2UgYXJlIE5DTlIgYWxsIHJlbWFpbmluZyBxdHkgd2lsbCBuZWVkIHRvIHNoaXAgdG8gR0hTUCBpbiBKYW51YXJ5LiBUaGVuIEdIU1Agd2lsbCBoYXZlIHRvIHN3aXRjaCB0byB0aGUgbmV3IHBhcnQgbnVtYmVyIGlmIHRoZXJlIGlzIGZ1cnRoZXIgZGVtYW5kLjwvRnV0dXJlX3gwMDI3X3NfeDAwMjBfc3RhdHVzX3gwMDIwX3VwZGF0ZXM+"
 },
 {
 "$content-type": "application/xml;charset=utf-8",
 "$content": "PEVuZF94MDAyMF9DdXN0b21lcl94MDAyMF9JbmZvPkdNPC9FbmRfeDAwMjBfQ3VzdG9tZXJfeDAwMjBfSW5mbz4="
 },
 {
 "$content-type": "application/xml;charset=utf-8",
 "$content": "PF94MDAzMV8tMTAwMDkxPjwvX3gwMDMxXy0xMDAwOTE+"
 },
 {
 "$content-type": "application/xml;charset=utf-8",
 "$content": "PE1FTD48L01FTD4="
 },
 {
 "$content-type": "application/xml;charset=utf-8",
 "$content": "PE1MWDgwMTA0S0xRLURBRy0wMDAtUkU+PC9NTFg4MDEwNEtMUS1EQUctMDAwLVJFPg=="
 }
 ]
}

But the output:

{
 "body": [
 {
 "": []
 },
 {
 "": []
 },
 {
 "": []
 },
 {
 "": []
 },
 {
 "": []
 },
 {
 "": []
 },
 {
 "": []
 },
 {
 "": []
 },
 {
 "": []
 },
 {
 "": []
 },
 {
 "": []
 },
 {
 "": []
 },
 {
 "": []
 },
 {
 "": []
 },
 {
 "": []
 },
 {
 "": []
 },
 {
 "": []
 },
 {
 "": []
 },
 {
 "": []
 }
 ]
}

What I'm doing wrong? Could someone give me a hand=

Thanks!

Categories:

General topics

Hi , @IvoF
Thanks for your response! I create a test Excel table in my side and the result is as follows:

This is my test flow you can refer to :

Select Headers:
xpath(xml(json(concat('{"root":{"value":', first( outputs('List_rows_present_in_a_table')?['body/value'] ) ,'}}'))), '//root/value/*[not(name()="ItemInternalId")]')
xpath(item(), 'name(//*)')

Select 2:
xpath( xml(json(concat('{"root":{"value":', outputs('List_rows_present_in_a_table')?['body/value'] ,'}}'))) , '//value/*[not(name()="ItemInternalId")]')
xpath(item(), 'name(/*)')
xpath( item() , concat('/',xpath(item(), 'name(/)'),'/text()' ) )?[0]

Select:
chunk(body('Select_2'),length(body('Select_Headers')))
json(concat('{"__metadata":{"type":"SP.Data.', replace(outputs('settings')['listName'], ' ', '_x0020') , 'ListItem"},' , replace( replace( join(item(),',') , '{' , '') , '}' ,'') ,'}' ))

If this reply can help you , you can click mark this reply as solution (Accept solution) which can help more people, thanks in advance!

Best Regards,

Yueyun Zhang