Help me Help me How to extract value only?

[minyoungchoe]

The one I want to extract is 'Value' of Analytical Categories.

which looks like below.

[{"@odata.type":"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference","Id":3,"Value":"Statistical/Forecasting"},{"@odata.type":"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference","Id":4,"Value":"Insights/Descriptive"},{"@odata.type":"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference","Id":10,"Value":"Classification"},{"@odata.type":"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference","Id":5,"Value":"Machine Learning/Predictive"}]

It never every gives me dynamic value so I already tried to use expression item()?['Analytical Categories']?['Value']

It returns null only.

Please help me ...

[minyoungchoe]

1. In the analytical categories, I want to put the value of analytical categories

2. It never gives me the value as below

3. If I choose Analytical Categories asitself above, it gives me below

[{"@odata.type":"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference","Id":3,"Value":"Statistical/Forecasting"},{"@odata.type":"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference","Id":4,"Value":"Insights/Descriptive"},{"@odata.type":"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference","Id":10,"Value":"Classification"},{"@odata.type":"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference","Id":5,"Value":"Machine Learning/Predictive"}]

[Matthy79]

I am not at a computer at the moment but the important thing is that you should think about what you really want in that csv. You don’t have one “Analytical Category”, there are multiple of those categories in each row. What should the csv look like?

[minyoungchoe]

This is what I am trying to do.

[Matthy79]

That is exactly what I am saying. There is not only one Value, there are multiple Values. In the sample you posted I see 4 different Value properties. You have to decide which one you want in the csv. If you want every value you will have to decide in which format you want them to show. Also this will not be that easy, because you can’t use join on that one.

[minyoungchoe]

I want to show all values. I thought it is possible ...

[Matthy79]

So you want a csv to have a column with line breaks as a delimiter for each category? Are you sure that makes sense? Do you maybe need an html table? What do you want to do with that csv?

[minyoungchoe]

My team will use the csv file once the flow that I am making is done.
Line breaks as a delimiter for each category should be fine.

[minyoungchoe]

No Html table.

[Matthy79]

Since we are talking about an array inside an array I see 2 options.

1.) Create the csv in a loop for each row manually which will take time

2.) Create it with help of xpath

I would suggest you check with your team if a line break is really what you want as delimiter, upload a sample csv how you want it to look like if there are more categories and I will have a look at it tomorrow. It is really late and I go to bed now.

[minyoungchoe]

Thanks, I am in Toronto. Sorry for late message...

[grantjenkins · Accepted solution]

Hopefully this will get what you're after. As @Matthy79 mentioned - the best way is to use some XPath to extract out the multiple choices.

For this example, I'm using the following SharePoint List.

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

Get items retrieves our list items.

Compose converts the data from Get items to XML so we can use XPath. The expression used is:

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

Create CSV table uses the output from Get items and the following expression for the Analytical Categories.

join(xpath(outputs('Compose'), concat('//root/value[ID=', item()?['ID'], ']/AnalyticalCategories/Value/text()')), decodeUriComponent('%0D%0A'))

I'm not sure how you are saving the CSV table (SharePoint Library, Email attachment, etc.) so I've just used Create file and saving in my SharePoint Library. Note that it uses the output from Create CSV table.

The final output is shown below.

[Agnius]

I will move this topic to the correct forums for Power Automate cloud flows. You posted it into the forums for desktop flows.

[Matthy79]

Hello @grantjenkins,

thank you for taking over. That is the way I would have done it.

[minyoungchoe]

Thanks for replying.
I am reading and reading and reading to understand how it works since it is my first time to use power automate.

May I ask more questions if I am stuck? 

Thanks... and thanks @Matthy79 as well..

[minyoungchoe]

Sorry, it is me again @Matthy79 @grantjenkins 

The output I expected is below

[

  {

    "@odata.type": "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",

    "Id": 3,

    "Value": "Statistical/Forecasting"

  },

  {

    "@odata.type": "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",

    "Id": 4,

    "Value": "Insights/Descriptive"

  },

  {

    "@odata.type": "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",

    "Id": 10,

    "Value": "Classification"

  },

  {

    "@odata.type": "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",

    "Id": 5,

    "Value": "Machine Learning/Predictive"

  }

]

And final output is something wrong.

I confirm that we have the Analytical Categories properly once Compose action done, look below.

[Matthy79]

What exactly is wrong with the output and can you share screenshots of your current flow setup and a flow run history.

[minyoungchoe]

# Flow

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

# Create csv detail with 

join(xpath(outputs('Compose'), concat('//root/value[ID=', item()?['ID'], ']/AnalyticalCategories/Value/text()')), decodeUriComponent('%0D%0A'))

#Run history - Compose

#Output in the CSV file for Analytical Categories which is I am struggling

[Matthy79]

But I can see a value there. Do you think the value is wrong or what exactly is the problem? Have you changed the size of the column to view all the data. You requested to have new line for each value.

[minyoungchoe]

I expected 4 values but I have 1 value for now.

[grantjenkins]

It might just be because the rows in your CSV need to have the height adjusted. I'm assuming the values are in the cells, but you just cant' see them.