Power Automate

Unanswered

PowerAutomate Issue during Select Variables step - CSV table

(0) Share

Report

Posted on by jessicadduffey

Hi Power Automate Community! Riddle me this (because I am SO confused):

I'm building a flow to take SharePoint form responses and export them into a CSV table. First, here is the form:

Expression in Apply to each/Select/Map:

if(empty(item()?['Value']),'empty',item()?['Value'])

Here are the Select 3 JSON inputs with the relevant response highlighted in red:

{
"@odata.etag": "\"1\"",
"ItemInternalId": "5779",
"ID": 5779,
"Created": "2021-09-08T19:23:05Z",
"CWID": "nhoma",
"First_x0020_and_x0020_Last_x0020": "Nick Homa",
"Where_x0020_are_x0020_you_x0020_": "Chesterfield, MO",
"What_x0020_are_x0020_the_x0020_t": "Learn more about LGBTQ+ legislation. Already active in ARISE and ARISE Ally leadership. ",
"How_x0020_did_x0020_you_x0020_he": [
{
"@odata.type": "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",
"Id": 1,
"Value": "Co-Worker"
}
],
"How_x0020_did_x0020_you_x0020_he@odata.type": "#Collection(Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference)",
"How_x0020_did_x0020_you_x0020_he#Id": [
1
],
"How_x0020_did_x0020_you_x0020_he#Id@odata.type": "#Collection(Int64)",
"BLEND": true,
"BLEND_x0020_Chapters": [
{
"@odata.type": "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",
"Id": 11,
"Value": "St. Louis"
}
],
"BLEND_x0020_Chapters@odata.type": "#Collection(Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference)",
"BLEND_x0020_Chapters#Id": [
11
],
"BLEND_x0020_Chapters#Id@odata.type": "#Collection(Int64)",
"Which_x0020_Business_x0020_Resou": [],
"Which_x0020_Business_x0020_Resou@odata.type": "#Collection(Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference)",
"Which_x0020_Business_x0020_Resou#Id": [],
"Which_x0020_Business_x0020_Resou#Id@odata.type": "#Collection(Int64)",
"What_x0020_are_x0020_the_x0020_t0": [],
"What_x0020_are_x0020_the_x0020_t0@odata.type": "#Collection(Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference)",
"What_x0020_are_x0020_the_x0020_t0#Id": [],
"What_x0020_are_x0020_the_x0020_t0#Id@odata.type": "#Collection(Int64)",
"{Identifier}": "Lists%252fBrg%252f5779_.000",
"{IsFolder}": false,
"{Thumbnail}": {
"Large": null,
"Medium": null,
"Small": null
},
"{Link}": "https://bayergroup.sharepoint.com/sites/Inclusion&DiversityNA/_layouts/15/listform.aspx?PageType=4&ListId=cf09d95c%2D1306%2D4183%2Dbddf%2Dc29c5526134b&ID=5779&ContentTypeID=0x0100157911574944A14D8F8F29051925FC72",
"{Name}": "",
"{FilenameWithExtension}": "(no title)",
"{Path}": "Lists/Brg/",
"{FullPath}": "Lists/Brg/5779_.000",
"{ContentType}": {
"@odata.type": "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedContentType",
"Id": "0x0100157911574944A14D8F8F29051925FC72",
"Name": "Item"
},
"{ContentType}#Id": "0x0100157911574944A14D8F8F29051925FC72",
"{VersionNumber}": "1.0"
},
{
"@odata.etag": "\"2\"",

From this input, it appears that the response (St. Louis) is pulling in as it should be. However, look at the output of the Select 3 step:

[

{

"Created": "2021-05-17T20:25:38Z",

"CWID": "SHEWL",

"First and Last Name": "Tazeem Dearnley",

"Where are you located": null,

"Division": null,

"What are the top things you hope to get out of BRG participation": "",

"What else do you hope to get out of BRG participation": null,

"BLEND": true,

"BLEND Chapters": "Berkeley Boston",

"Preferred Communication Mode": null,

"Would you be interested in volunteering for US BRG roles": null

{

"Created": "2021-05-18T12:26:31Z",

"CWID": "GDWXA",

"First and Last Name": "Sarah Dreikorn",

"Where are you located": null,

"Division": null,

"What are the top things you hope to get out of BRG participation": "",

"What else do you hope to get out of BRG participation": null,

"BLEND": true,

"BLEND Chapters": "Berkeley Boston",

"Preferred Communication Mode": null,

"Would you be interested in volunteering for US BRG roles": null

{

"Created": "2021-05-19T18:09:18Z",

"CWID": "GGOVL",

"First and Last Name": "Tracy Lea",

"Where are you located": "Whippany",

"Division": null,

"What are the top things you hope to get out of BRG participation": "",

"What else do you hope to get out of BRG participation": "Top reason: Culture: I want to support and enable a more inclusive work environment. Other reason: possible community engagement with philanthropy and advocacy.",

"BLEND": true,

"BLEND Chapters": "Berkeley Boston",

"Preferred Communication Mode": null,

"Would you be interested in volunteering for US BRG roles": null

...and ALL of the BLEND Chapters responses are feeding into the CSV table as Berkeley Boston???

Here is a screenshot of the question from the SharePoint form (BLEND Chapters):

What could be going on here? Why is all the data changing to Berkeley Boston in the Select Variables step?

Thank you so much for your help - any questions lmk.

Categories:

Building flows

I have the same question (0)

All responses (5)

Answers (1)

v-xiaochen-msft on at

Like (0)

Report

Hi @jessicadduffey ,

Please try this

{"Title":@{items('Apply_to_each')?['Title']},"TestLookup":@{body('Join')}}

Best Regards,

Wearsky

Was this reply helpful? Yes No
jessicadduffey on at

Like (0)

Report

@v-xiaochen-msft sorry, this doesn't appear to be working...
Screenshots of amended flow:
Screenshot of how this feeds into the CSV file:
JSON input from Select section:
{
            "@odata.etag": "\"1\"",
            "ItemInternalId": "5334",
            "ID": 5334,
            "Created": "2021-05-19T18:09:18Z",
            "CWID": "GGOVL",
            "First_x0020_and_x0020_Last_x0020": "Tracy Lea",
            "Where_x0020_are_x0020_you_x0020_": "Whippany",
            "What_x0020_are_x0020_the_x0020_t": "Top reason:  Culture:  I want to support and enable a more inclusive work environment.   Other reason: possible community engagement with philanthropy and advocacy.",
            "How_x0020_did_x0020_you_x0020_he": [
                {
                    "@odata.type": "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",
                    "Id": 0,
                    "Value": "Bayer Intranet Article"
                }
            ],
            "How_x0020_did_x0020_you_x0020_he@odata.type": "#Collection(Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference)",
            "How_x0020_did_x0020_you_x0020_he#Id": [
                0
            ],
            "How_x0020_did_x0020_you_x0020_he#Id@odata.type": "#Collection(Int64)",
            "BLEND": true,
            "BLEND_x0020_Chapters": [
                {
                    "@odata.type": "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",
                    "Id": 8,
                    "Value": "New Jersey"
                }
            ],
            "BLEND_x0020_Chapters@odata.type": "#Collection(Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference)",
            "BLEND_x0020_Chapters#Id": [
                8
            ],
            "BLEND_x0020_Chapters#Id@odata.type": "#Collection(Int64)",
            "Which_x0020_Business_x0020_Resou": [],
            "Which_x0020_Business_x0020_Resou@odata.type": "#Collection(Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference)",
            "Which_x0020_Business_x0020_Resou#Id": [],
            "Which_x0020_Business_x0020_Resou#Id@odata.type": "#Collection(Int64)",
            "What_x0020_are_x0020_the_x0020_t0": [],
            "What_x0020_are_x0020_the_x0020_t0@odata.type": "#Collection(Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference)",
            "What_x0020_are_x0020_the_x0020_t0#Id": [],
            "What_x0020_are_x0020_the_x0020_t0#Id@odata.type": "#Collection(Int64)",
            "{Identifier}": "Lists%252fBrg%252f5334_.000",
            "{IsFolder}": false,
            "{Thumbnail}": {
                "Large": null,
                "Medium": null,
                "Small": null
            },
            "{Link}": "https://bayergroup.sharepoint.com/sites/Inclusion&DiversityNA/_layouts/15/listform.aspx?PageType=4&ListId=cf09d95c%2D1306%2D4183%2Dbddf%2Dc29c5526134b&ID=5334&ContentTypeID=0x0100157911574944A14D8F8F29051925FC72",
            "{Name}": "",
            "{FilenameWithExtension}": "(no title)",
            "{Path}": "Lists/Brg/",
            "{FullPath}": "Lists/Brg/5334_.000",
            "{ContentType}": {
                "@odata.type": "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedContentType",
                "Id": "0x0100157911574944A14D8F8F29051925FC72",
                "Name": "Item"
            },
            "{ContentType}#Id": "0x0100157911574944A14D8F8F29051925FC72",
            "{VersionNumber}": "1.0"
        },

Screenshot of portion of JSON output from Select section:

Any suggestions? Thank you for your help!

Was this reply helpful? Yes No
Verified answer

v-xiaochen-msft on at

Like (0)

Report

Hi @jessicadduffey ,

There is no need to use 'select' after 'Apply to each'.

If you want to add more columns, please add them here instead of using variables in select action.

Best Regards,

Wearsky

Was this reply helpful? Yes No
jessicadduffey on at

Like (0)

Report

@v-xiaochen-msft , I made this correct and the flow appears to be working well with one exception - passing through "null" values. What would you suggest here? Would you recommend this statement in the select step, or something different:
if(empty(item()?['Value']),'no response',item()?['Value'])

Was this reply helpful? Yes No
v-xiaochen-msft on at

Like (0)

Report

Hi @jessicadduffey ,

It's ok with the expression you provided.

If you want it to be more perfect, please try:

if(empty(items('Apply_to_each')?['Column Name']),'no response,'items('Apply_to_each')?['Column Name'])

Best Regards,

Wearsky

Was this reply helpful? Yes No