Get column name when it is equal value X

[BeSe]

Hello, glad I have found this forum. I am pretty much a newbie to PowerAutomate but I think it is a great tool.

I use Get-items with ODATA filter to get a specific line from a SharePoint list. This step is successful.

My problem: From this line I want to get the column name if the value is a certain string, let's say "pick me". So "pick me" is always the same string but in different lines it is in different columns. I don't know which action I shall use as it seems to me you always have to use a fixed known column for a condition matching a specific value or not. But as said, the column is not fixed, only the value.

Hope it is understandable.

BR

[v-yueyun-msft]

Hi , @BeSe 

According to your description, you want to get the columnNames which contains the "pick me " value .

This is my test SharePoint List:

This is my flow result :

This is my flow:

(1)The "Get items","Select","Initial variable" action:

(2)The "Apply to each" action :

replace(replace(string(items('Apply_to_each')),'{',''),'}','')

split(outputs('Compose'),',')

replace(split(item(),':')[0] , '"','')

items('Apply_to_each')?[items('Apply_to_each_2')]

(3)The last action:

union(variables('ColumnNames'),variables('ColumnNames'))

Best Regards,

Yueyun Zhang

[BeSe]

Hi @v-yueyun-msft ,

thanks for the fast and detailed reply!

First of all an information or potential trap: I have tried to create your steps in the new designer and it gave me syntax errors right in the very first compose-action. I continued in the old designer and it was accepted right away.

Anyway, the flow looks amazing but does not give me the desired result. Keypoint is the "Select 2"-action. Input gives this:

[
"\"col1\":[\"@odata.type\":\"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference\"",
"\"Id\":0",
"\"Value\":\"random\"]",
"\"col2\":[]",
"\"col3\":[\"@odata.type\":\"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference\"",
"\"Id\":1",
"\"Value\":\"pick me\"]",
"\"col4\":[]",
"\"col5\":[]",

]

Output cuts to much so the value "pick me" is removed and can not match the condition in the next step:

[
"col1",
"Id",
"Value",
"col2",
"col3",
"Id",
"Value",
"col4",
"col5",
]

BR

[v-yueyun-msft]

Hi , @BeSe 

Thanks for your quick response! This is my Compose action output in my Apply to each loop:

And this is my Select2 action Input:

Can you show me what is your output to loop in your Apply to each in your run history? [Like the Select action output]

And i am not surely understand your "Output cuts to much so the value "pick me" is removed and can not match the condition in the next step".Can you give me some sample data and your end result you need in your side ?

Best Regards,

Yueyun Zhang

[BeSe]

Hi @v-yueyun-msft ,

this is my Select action output (so the first one which is not in a loop according to your flow):

[
{
"col1": [
{
"@odata.type": "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",
"Id": 0,
"Value": "random"
}
],
"col2": [],
"col3": [
{
"@odata.type": "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",
"Id": 1,
"Value": "pick me"
}
],
"col4": [],
"col5": []
}
}
]

The Input and Output of the Select 2 (which is in the loop) I gave you in my previous post. That is what I meant - the output cuts the values (e.g. the important one "pick me").

So my end result according to my example should be "col3" cause in that column there is the value "pick me".

One question: What is your compose-action there for? If I understand correctly it should remove { and } but I don't see it in my input anyway. And in yours of your last screenshot I don't see any as well?

I really appreciate your help. Thanks a lot.

BR

[Chriddle]

What about this?

Constrains:

• Finds only the first "pick me" in a row.
• If your col names contain special characters (like space) you have to decode them.

Map Id:

item()['Id']

Map ColName:

xpath(
	xml(json(concat('{"Root":', item(), '}'))),
	'name(//*[.="pick me"])'
)

Output:

[
  {
    "Id": 0,
    "ColName": "Col0"
  },
  {
    "Id": 1,
    "ColName": "Col1"
  },
  {
    "Id": 2,
    "ColName": "Col2"
  },
  {
    "Id": 3,
    "ColName": ""
  }
]

[v-yueyun-msft]

Hi , @BeSe 

Thanks for your quick response! Yes, you need to convert your input to this :

If you have this input :

You can use the select to extract the value you need :

 For explanation, apply to each this array , one of it is like this:

So we can replace the { and } to  '' , will replace to this:

Then we can split by the ','  , and usethe select action to extract the COlumnNames as an array:
["col1","col2","col3","col4","col5"]

Then we can apply to this COlumnNames Array to judge if contains the special value.

Best Regards,

Yueyun Zhang

[v-yueyun-msft]

Hi , @BeSe 

I also test it in my side , it works good from your example data :

[
  {
    "col1": [
      {
        "odatatype": "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",
        "Id": 0,
        "Value": "random"
      }
    ],
    "col2": [],
    "col3": [
      {
        "odatatype": "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",
        "Id": 1,
        "Value": "pick me"
      }
    ],
    "col4": [],
    "col5": []
  },
  {
    "col1": [
      {
        "odatatype": "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",
        "Id": 0,
        "Value": "random"
      }
    ],
    "col2": [],
    "col3": [],
    "col4": [
      {
        "odatatype": "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",
        "Id": 1,
        "Value": "pick me"
      }
    ],
    "col5": []
  }
]

Best JSON Formatter and JSON Validator: Online JSON Formatter

This code is the same my first reply above.

And the result is as follows:

Best Regards,

Yueyun Zhang

[BeSe]

Hello @Chriddle ,

thanks for your reply. I get an error when trying out claiming property 'Id' does not exist and available properties are only col1,col2...(please be aware my actual column names are not incremented col[n] but different strings.) I think it is because your array is built differently compared to mine and I am afraid I don't know how to adapt your solution to mine.

BR

[BeSe]

Hi @v-yueyun-msft ,

for me it almost works. I get an error for the columns that got no actual data in it like:

"col2": [],

So I am surprised it works in your case with my sample? Anyway seems like only a small step and I will try to solve this tomorrow. Just wanted to give a current status.

BR

[Chriddle]

In my suggestion, the actual column names don't matter, except for Id.

I assumed that you also need to identify the row in which "pick me" occurs.

So you need to change "Id" to your row id column name (or remove this map from the selection if it's not needed).

[v-yueyun-msft]

Hi , @BeSe 

Thanks for your quick response!

For your length=0 array , you need to extract the json add the '?' to avoid the error.

If I misunderstand what you mean, you can describe your needs and your expected outcomes in detail so that we can better help you.

Best Regards,

Yueyun Zhang

[BeSe]

The actual column name is my desired result.

How shall I change "Id" to my column name when I do not know which name I need in that state? Sorry, don't get it. If I leave mapping of Id it gives me this:

[
  {
    "ColName": ""
  }
]

[BeSe]

Hi @v-yueyun-msft ,

I have already created it exactly to your screenshot, including the '?' you have marked in your screenshot. So including ?[0]? covers if the array=0 or how could one explain it? If so, the array does not equal 0 it is [] e.g. example "col2":

So if I only map columns with actual values in it, it works. But I do need to cover the case the column shows me '[]'.

This is the failure I get with my actual named columns:

BR

[v-yueyun-msft]

Hi , @BeSe 

First,sorry for my delay response due to weekend.

Thanks for your quick response! According to your error code :

This means your [DKR] is not an array , it is an Object . So what is your actually [DKR] in your Json? 

And for your example ,it has only col1,col2,col3,col4,col5.... I have not found your [DKR].

If  possible , can you give me your sample json and your end result to me so that i can fully understand your need!

Best Regards,

Yueyun Zhang

[BeSe]

Hi @v-yueyun-msft ,

sorry for the confusion. Maybe I have not pointed out too clearly - last screenshot was my prod data, before this I used example terms for my problem. But the variable definitions are the same, so it makes no difference. However, I screenshoted my real data and put some comments on it, hopefully this makes it clear:

Please be aware of that in my posted failure message every column is mentioned:

"Select_only_column_names_and_according_values" is the select-action you posted on thursday.

So to my understanding the problem is not DKR, but the columns that have no data (-> e.g. "CHZ": []...) in it....because if I only map columns with data it works.

BR

[v-yueyun-msft]

Hi , @BeSe 

Thanks for your quick response! For your screenshot , we can see that :

So in the Select Action , if you need to extract the [CKR] and [TKU] field, you need to use this code :
item()?['CKR']?['Value']

item()?['TKU']?['Value']

You can not use the item()?['CKR']?[0]?['Value'] due to it is not an array in json.

Best Regards,

Yueyun Zhang

[BeSe]

Hi @v-yueyun-msft ,

thank you for you fast reply!

Ok, got this. But I need a function that can handle both cause as said initially the values change. Cause this output equals one week which is one row. Next week, so in the next row the values will be different - then CHZ is not empty anymore but e.g. DKR and TKU are. Do I need to convert every column first in either an object or an array? I guess this has something to do with SharePoint's list defaults to set empty columns as an array and not an object?

Cause I did not manipulate anything, I followed your advisories you offered. BR

[v-yueyun-msft]

HI , @BeSe 

Do you mean you want to handle the blank array or blank object? As the expression in PowerAutomate ,like :
item()?['CKR']?['Value']

If your CKR column has no object , it will return null without the error code in my understand due to the ? in the Expression.

If I misunderstand what you mean, you can give me some sample data and your expected outcomes in detail so that we can better help you.

Best Regards,

Yueyun Zhang

[BeSe]

Hey @v-yueyun-msft ,

I changed it to your suggestion and now I got this error:

BR