A value must be provided for item - Export to Excel

[AndreasHoeppi]

My problem was often handled, but I still can't fix it. The problem should be in the "Add a row into a table" ("Zeile zu Tabelle hinzufügen"):

Input:

{
  "Labornummer": "121",
  "pH": 2,
  "LF": 2,
  "AbsSt": 2,
  "Datum": "2023-08-03",
  "Analytiker": {
    "@odata.type": "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",
    "Id": 0,
    "Value": "AG"
  },
  "Prüfmittel": [
    {
      "@odata.type": "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",
      "Id": 0,
      "Value": "WT000356"
    },
    {
      "@odata.type": "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",
      "Id": 1,
      "Value": "WT000479"
    }
  ],
  "Anmerkungen": "-"
}

Output:

{
  "status": 400,
  "message": "A value must be provided for item.\r\nclientRequestId: aa06b061-fa9a-4cac-b5ce-40d844ba653a",
  "error": {
    "message": "A value must be provided for item."
  },
  "source": "excelonline-ne.azconn-ne-003.p.azurewebsites.net"
}

There are no special characters in the column names or anything unusual ...

[Matthy79]

If you look at "Analytiker" and "Prüfmittel" you try to add an Object and an array to the cell. I don't think this is possible. What should be in those cells in your example?

[AndreasHoeppi]

Hi @Matthy79

Analytiker: there should be 1 value of a drop down-field:

in Prüfmittel: there should be 1 or more value(s) of a drop down-field:

in the end in this cells there should be a small text. but you're right.. when i delete those 2 items, the export would work fine.. however, I hope this is fixable

[Matthy79]

Hello @AndreasHoeppi

please provide a screenshot of how you would like to be the result in excel. I do not think that you can store an array in excel. You can of course join an array to make more values to be one. They can be separated with newline, comma or whatever, but in fact there is only one value stored.

[AndreasHoeppi]

Hi @Matthy79 

this should be the result:

[Matthy79 · Accepted solution]

I'm not 100 percent sure if this is possible with your current flow setup, because I don't know a formula that would extract a simple array within a select action.

I would suggest you don't apply to your select but apply to each "Elemente abrufen". You than use a select statement inside the loop to extract the values of "Prüfmittel" and use a "Compose" (Verfassen) action to create your object for the "Zeile zu Tabelle hinzufügen" action. For Analytiker you simply select the Value inside the Analytiker object. For Prüfmittel you use "join(body('Select'),',')".

Please try it and if you don't have any success, dann schreib mich über PA an.