how to grab all column names using a string concatenated variable that changes on every loop iteration

[dperez13]

I have a sharepoint online list with a bunch of choice column types. I need to grab all of those that are 'false', (which I dont imagine is too hard to do, but I need to do that step first for the trickier step 2: so,  in addition to that , I need to grab all their corresponding 'explanation' columns (text columns). The one good thing about the data is that each column thats a choice of true or false, also has a corresponding column attached to it with identical wording. So question 1(true or false) has a question 1 explanation column attached to it.

so my spo column field data looks like this

question 1, question 1 explanation, question 2, question 2 explanation

So my idea was to make a for loop in power automate that finds the column name where the value is false/no/whatever and then take that column name, add a " " and a "explanation" after it using some string manipulation, and than find the corresponding column with that variable name

so the psuedo logic would look like this:

For each column where value = whatever

columnToGrab = column name & " explanation"

map?select? : columntoGrab value

The last  line are what im not sure how to do ,what activity would I use to grab that columnToGrab variable each time in a loop?

-thanks

[grantjenkins · Accepted solution]

I'm not entirely sure what you want to do with your output, but hopefully this should get you started. I'm converting the JSON to XML and using XPath to retrieve the column names of the questions that are set as Yes. You can add as many questions as you like, and the flow will still work as expected.

Below is the list I'm using for this example.

See full flow below. I'll go into each of the actions.

Get items retrieves all the items from the list.

XML is a Compose that converts the items returned as XML so we can use XPath. Note that we also add a root element to ensure it's valid XML. The expression used is:

xml(json(concat('{"root": { value:', outputs('Get_items')?['body/value'], '}}')))

Apply to each iterates over each of the items returned.

Names is a Compose that uses XPath to return the Names of the columns that start with "Question" where the Value is set as Yes. The expression used is:

xpath(outputs('XML'), concat('//value[ID="', items('Apply_to_each')?['ID'], '"]/*[starts-with(name(), "Question") and starts-with(./Value, "Yes")]'))

Select takes the output from Names and uses the following expressions to extract out the Name and text from the corresponding Explanation column:

//Name
xpath(item(), 'name(//*)')

//Value
items('Apply_to_each')?[concat(xpath(item(), 'name(//*)'), 'explanation')]

For this example, I get the following output.

//First list item
[
  {
    "Question1": "Test 001 - Question 1 Explanation"
  }
]

//Second list item
[
  {
    "Question1": "Test 002 - Question 1 Explanation"
  },
  {
    "Question2": "Test 002 - Question 2 Explanation"
  }
]

[dperez13]

thank you obi-wan kenobi, you are my only hope, but I must apologize slightly, but I do see that you did comment on my other post, and this relates to that one as its the same issue. im vaguely familiar with xml and not at all with xpath, but this is pretty exciting to implement, HOWEVER, I must apologize my table isn't actually formatted in a way where every column starts with question, its more like:

xyz, xyz explanation, abc,abc explanation,

HOWEVER

my internal field names (having been uploaded from an excel are as follows)

field_1,field_2,field_3,field_4

This is why In my head I was imagining a counter variable , if field_1 = yes,  fetch counter+1, else move to check fieldname + concat counter +2. but I dont think we need to go this route anymore, I think with your code above, I can perhaps modify your code above and instead of 

"Names of the columns that start with "Question" where the Value is set as Yes"

we can go through all, as they are all yes/no questions (100 of them) plus their explanation fields (100 of them)

i need to read up on XPATH  but can we change 

/*[starts-with(name(), "Question")

to *(wildcard selector for ALL?) 

and leave the rest after it the same? the code is really foreign to me so im not sure where to begin but i can play around with it

:

* and starts-with(./Value, "Yes")]'))

and also will I still need this line of code?

('Apply_to_each')?['ID']

if this flow will always every apply to only one ID (get items filters on one unique ID per run)? just curious, because I know in the screenshot I posted, there is an internal ID of (-1?) for each column/field  , not sure if those are referenced or not? 

here is the json, from the screenshot from the other thread you posted in: 

https://powerusers.microsoft.com/t5/Building-Flows/How-do-I-put-a-variable-inside-of-dynamic-content...

the data seems to be structured as value [ : metafields, ID of the entire row, columnName: { odataType,ID:-1 (for all of them) and than the important part: Value: Pass/Fail (yes/no)

xml(json(concat('{"root": { value:', outputs('Get_items')?['body/value'], '}}')))

im wondering if this line of json will go deep enough into the structure to get that value of pass/fail/yes/no. im checking it out now -thanks! but if it doesnt, how do i traverse deeper down into json when the parent root is dynamic on something like this?

edit:

also if need be I dont mind remaking my entire list by throwing QUESTION in front of every field, that is both the choice field and the single line text explanation field, if that will make this code work.

as of now my columns look like

Apples: choice1/choice2

Apples explanation: user comments here to Grab

Oranges : choice1/choice2

Oranges explanation: user comments here to Grab

[grantjenkins]

@dperez13 I'm working on a new version to cater for questions that start with anything 🙂

[dperez13]

Also what is 'name'? in this context

//Name
xpath(item(), 'name(//*)')

//Value
items('Apply_to_each')?[concat(xpath(item(), 'name(//*)'), 'explanation')]

ive never seen you reference anything called 'name' earlier, nor after. I saw a compose named 'Names', but im curious what this does and what it applies to? Is it some hardcoded json thing that xml extracts as 'name'?

i tried it all, with the only change of switching starts with question to starts with field, but the xml input seems to have the data in there, but everything inside the apply to each is blank.

[grantjenkins]

@dperez13 Just changing the XPath expression for the Names should cater for all questions regardless of their name (not just starting with Question). I did a quick test, and all seems to be working.

xpath(outputs('XML'), concat('//value[ID="', items('Apply_to_each')?['ID'], '"]/*[starts-with(./Value, "Yes")]'))

[grantjenkins]

name() will extract the name (key) of the element, where text() will extract the actual value.

[dperez13]

 I think were in business, nice. just some further testing on my end, but it did pull a field!

because its in a for each / apply to each "loop" does that mean it will run the select operation many times. i guess my next step would be to have it perhaps push all of the output into a variable, perhaps an append string with a space after each output (i already saw another post on doing this), so this I can manage. 

but back to point one, we are not mapping just one table with the select activity, we are mapping one table per 'finding' if you will, right? thats fine by me, as long as I can then take all my findings and put them in one place, say, the body of an email, or append them as I mentioned earlier to a variable each time the loop runs.

edit: after some testing, Im seeing it only picked up one column and didnt go to the rest.. could this be a power automate limitation with the sheer number of columns>? 

edit: nevermind, it pulled both. im seeing the loop only runs once and pulls many, in my mind I thought the apply to each loop runs for every field, but im realizing thats not the case. the loop runs once and fetches all the fields in the output. what it has done. so far though the output does grab the correct column names, but the internal names, and not the corresponding explanations column. but I have a fix for this in mind already.

in power Apps, anytime someone writes in the explanation text field, ill add a special string at the end. so if the column isnt blank, it will populate like so : "user comments XX". if the value ends with XX instead of starts with YES, grab as well. that should solve that.

for the record, my output looks like this:

[

{

"field_17": null

}

{

"field_19": null

}

]

so it did grab the two columns where 'yes' was there, but brought back their internal names, which may not be too helpful for the end user, but its a start

[dperez13]

are you saying text() will grab yes/no, or text in this case will grab the actual column name and name() grabs its internal name, like field_15 , for example?

[dperez13]

if i wanted to grab yes and for the explanation side, i wanted to grab those columns, would I do an and here and throw it in power automate,  or would it be in-line and inserted into the existing code that fetches for values where 'yes' is present?

Im thinking of this

xpath(outputs('XML'), concat('//value[ID="', items('Apply_to_each')?['ID'], '"]/*[starts-with(./Value, "Yes")]'))

but also id like to grab the corresponding explanation columns, (i guess order doesnt matter) so these as well

xpath(outputs('XML'), concat('//value[ID="', items('Apply_to_each')?['ID'], '"]/*[ends-with(./Value, "ZZ")]'))

 (im using power automate to populate all explanations columns with a corresponding choice 'yes' value to append/end with ZZ

[dperez13]

also

items('Apply_to_each')?[concat(xpath(item(), 'name(//*)'), 'explanation')]

what is the point of xpath(item(),name) here? shouldnt we concat first and than grab the path to the column?

were getting the path to the question, and then adding question+explanation to the end, and that remains with the path to the new column? 

am i right to assume that you could first concat name and explanation, and than grab the xpath of the item()?

if not, I guess I understand why, as we need to pull a valid path first before adding more string to it (explanation) and getting ourselves the new path?

[dperez13]

items('Apply_to_each')?[xpath(item(), 'name(//*)')]

with this code i was able to not pull Null and I was able to pull the data of the choice column, so

field_17 : {

odata.type:

id: -1

Value: Fail

} 

could you tell me how to drill deeper and grab the value from here?

im thinking its this?:

items('Apply_to_each')?[xpath(item(), 'name(//*)')]?['Value']

edit: okay, the above worked so now i have the data grabbing the internal field name, and that its a fail, which is perfect.

now i just need to grab the corresponding 'explanation' column value as well...

but I do understand you wrote this for me to grab the value of a text column, which doesnt have a nested json object inside of it, like the one I just wrote out up above, and that is more important than the above data

so that is my main ask, how do I grab those comments exactly? I have tried putting the internal name of the field directly, just as a test to grab the comments, and I have failed repeatedly:

//Value
items('Apply_to_each')?[concat(xpath(item(), 'name(//*)'), 'explanation')]

 could you please show me how to hardcode a column name in here to grab its text value? For example, "Question 1 explanation" or field_19. ive tried both and i keep getting null:

//Value items('Apply_to_each')?[xpath(item(), 'Question 1 Explanation']

[dperez13]

can you write a similar example with xml that grabs columns field values where the value ends or starts with a certain string?

[dperez13]

how would I write this to grab ALL the explanation columns, not the questions columns that have a json object inside of them? after ive grabbed all the explanation column I can have the logic at the end that checks if they start/end-with a certain string.

[dperez13]

also the issue is your column names seem to be your column names as they appear, my column names dont appear as such

so name() + explanation = "field_18 explanation"

when in reality, its field_19

[dperez13]

xpath(outputs('XML'), concat('//value

is this line looking for whats in the <value> tag of xml? im noticing my comments are in the <field_xx> tags in the xml, since <value> is fixed and <field_xx> is always different, instead of looking for <value> can we look for any xml tag that starts with "field" and whose .VALUE (value?) ends or starts with a certain string, say XX?

[dperez13]

xpath(item(),'string(//field_18/text())') 

i learned this from your thread here 

https://powerusers.microsoft.com/t5/Building-Flows/Export-to-Excel-or-CSV-SharePoint-choice-column/m...

this gets me the explanation field value. thats great. now the need is to make field_18 dynamic to fetch it from the xml

OR

a need to find a way to reference all strings that start with field or something of that nature

concat('string(//, [starts-with("field")] 

is something like this possible?

[dperez13]

https://powerusers.microsoft.com/t5/Building-Flows/How-to-insert-variable-in-power-automate-xpath-ex...

so im going to use this to put a dynamic variable here

xpath(item(),'string(//field_18/text())') 

string(

/*[name()="@{variables('myVariable')}"]

)

like so, but I was wondering if there a way to check in a condition if the following is true so I can know how to increment the variable accordingly'?

is there anyway to check a statement like this in a condition to see if the value does indeed

xpath(outputs('XML'), concat('//value[ID="', items('Apply_to_each')?['ID'], '"]/*[starts-with(./Value, "Yes")]'))

 start with "Yes" , and then proceed to do an action based on that condition/if statement? otherwise I wont be able to update the variable to the correct field value

[dperez13]

actually, I think the best way to do it is two flows, one that grabs where value starts with 'YES' and one that grabs where value ends with 'XX' , and if it does, pass that name() into the select to show the 'explanation'.

im just unclear how to format the bit after

xpath outputs xml to find the //root/value or //value of the single line of text and not the choice column?

[grantjenkins]

I kept thinking that the internal column names were the same except for the "explanation" at the end. I'll work on a different approach tomorrow for you (after midnight for me at the moment).