String to Table with Multiple columns

[BhagvanReddy1]

Hi Everyone,

I need help to convert string to Table format, I have a list which is storing last modified values. So i am fetching values against each item, string is in following format

"[{\"Change Date\":\"2023-01-28\",\"Change Field\":\"Description\",\"Previous Value\":\"<p>123 Abcdefghi</p>\",\"Changed Value\":\"<p>123 Abcdefghij1</p>\",\"Who Performed\":\"User1\"}]

[{\"Change Date\":\"2023-01-28\",\"Change Field\":\"Description\",\"Previous Value\":\"<p>123 Abcdefghij1</p>\",\"Changed Value\":\"<p>123 Abcdefghij12</p>\",\"Who Performed\":\"User2\"}]

[{\"Change Date\":\"2023-01-28\",\"Change Field\":\"Description\",\"Previous Value\":\"<p>123 Abcdefghij12</p>\",\"Changed Value\":\"<p>123 Abcdefghij123</p>\",\"Who Performed\":\"User 3\"}]

[{\"Change Date\":\"2023-01-28\",\"Change Field\":\"Title\",\"Previous Value\":\"<p>12abcdefgh</p>\",\"Changed Value\":\"<p>12abcdefgh1</p>\",\"Who Performed\":\"User 1\"}]"

Change Date	Field	Previous values	Changed Values	Modied
2023-01-28	Description	123 Abcdefghi1	123 Abcdefghij12	User 1
2023-01-28	Description	123 Abcdefghi	123 Abcdefghi1	User 2

Tried with split & forall functions but didn't succeed. Can someone help me?.

Thanks 🙂

@RezaDorrani , @WarrenBelz 

[WarrenBelz · Accepted solution]

@BhagvanReddy1 ,

Sunday morning and noting more productive to do, so I built a model and the this works in my test

With(
   {
      wTable: 
      Substitute(
         Substitute(
            Substitute(
               Substitute(
                  Substitute(
                     Substitute(
                        Substitute(
                           Substitute(
                              Substitute(
                                 YourTextHere,
                                 "{",
                                 ""
                              ),
                              "}",
                              ""
                           ),
                           "\",
                           ""
                        ),
                        "<p>",
                        ""
                     ),
                     "</p>",
                     ""
                  ),
                  """",
                  ""
               ),
               Char(10) & Char(10),
               "|"
            ),
            "[",
            ""
         ),
         "]",
         ""
      )
   },
   ShowColumns(
      AddColumns(
         Split(
            wTable,
            "|"
         ),
         "Change Date",
         Last(
            Split(
               Index(
                  Split(
                     Result,
                     ","
                  ).Result,
                  1
               ).Result,
               ":"
            )
         ).Result,
         "Change Field",
         Last(
            Split(
               Index(
                  Split(
                     Result,
                     ","
                  ).Result,
                  2
               ).Result,
               ":"
            )
         ).Result,
         "Previous Value",
         Last(
            Split(
               Index(
                  Split(
                     Result,
                     ","
                  ).Result,
                  3
               ).Result,
               ":"
            )
         ).Result,
         "Changed Value",
         Last(
            Split(
               Index(
                  Split(
                     Result,
                     ","
                  ).Result,
                  4
               ).Result,
               ":"
            )
         ).Result,
         "Who Performed",
         Last(
            Split(
               Index(
                  Split(
                     Result,
                     ","
                  ).Result,
                  5
               ).Result,
               ":"
            )
         ).Result
      ),
      "Change Date",
      "Change Field",
      "Previous Value",
      "Changed Value",
      "Who Performed"
   )
)

and in case you are wondering, you cannot create field names dynamically.

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

MVP (Business Applications)   Visit my blog Practical Power Apps

[BhagvanReddy1]

Thanks 😃 for the quick reply, whereas above functionality will return single value of it in table format. where as it should return multiple values. 

ClearCollect(colTable,
With(
{
HistoryTable:
Substitute(
Substitute(
Substitute(
Substitute(
Substitute(
Substitute(
Substitute(
Substitute(
Substitute(
VarResult,
"{",
""
),
"}",
""
),
"\",
""
),
"<p>",
""
),
"</p>",
""
),
"""",
""
),
Char(10) & Char(10),
"|"
),
"[",
""
),
"]",
""
)
},
ShowColumns(
AddColumns(
Split(
HistoryTable,
"|"
),
"Change Date",
Last(
Split(
Index(
Split(
Result,
","
).Result,
1
).Result,
":"
)
).Result,
"Change Field",
Last(
Split(
Index(
Split(
Result,
","
).Result,
2
).Result,
":"
)
).Result,
"Previous Value",
Last(
Split(
Index(
Split(
Result,
","
).Result,
3
).Result,
":"
)
).Result,
"Changed Value",
Last(
Split(
Index(
Split(
Result,
","
).Result,
4
).Result,
":"
)
).Result,
"Who Performed",
Last(
Split(
Index(
Split(
Result,
","
).Result,
5
).Result,
":"
)
).Result
),
"Change Date",
"Change Field",
"Previous Value",
"Changed Value",
"Who Performed"
)
)
)

[WarrenBelz]

@BhagvanReddy1 ,

I assume you have sorted this - my model below with the input (from a field in my test list) at the top and the output at the bottom.

[BhagvanReddy1]

From SharePoint i am fetching details of the record, the column which stores data is an multiline plain text,

here is my formula on App start

find On screen visible property

i can see only single value of the above result

Did i miss something?. Let me know

[WarrenBelz]

@BhagvanReddy1 ,

I will give you a clue which I will help if you do not find it - I simply copied the code from your post and worked with that. Have a look at the difference in spacing between records on what you have posted now and what I copied (it is in the top of my solution post) - there is only one thing you need to change.

[BhagvanReddy1]

I found it there was space in between each array element, apart from that didn't find anything, Is that the one?

[WarrenBelz]

@BhagvanReddy1 ,

When I copied your code from your Post, there was a line between each of the records, meaning two hard returns

Char(10) & Char(10)

which I then replaced with a pipe | which is used as the delimiter between the records

 Char(10) & Char(10),
 "|"

and then to split them

Split(
   wTable,
   "|"
)

So whatever is between your records (I suspect a single Char(10) ) needs to go in the second code above.