Answered

String to Table with Multiple columns

(0) Share

Report

Posted on by 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\":\"123 Abcdefghi\",\"Changed Value\":\"123 Abcdefghij1\",\"Who Performed\":\"User1\"}]

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

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

[{\"Change Date\":\"2023-01-28\",\"Change Field\":\"Title\",\"Previous Value\":\"12abcdefgh\",\"Changed Value\":\"12abcdefgh1\",\"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

Categories:

Power Apps component framework

Power Platform Build Tools

All responses (7)

Answers (1)

WarrenBelz 147,161 Most Valuable Professional on at

Like (0)

Report
Re: String to Table with Multiple columns

@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.
BhagvanReddy1 8 on at

Like (0)

Report

Re: String to Table with Multiple columns

I found it there was space in between each array element, apart from that didn't find anything, Is that the one?
WarrenBelz 147,161 Most Valuable Professional on at

Like (0)

Report

Re: String to Table with Multiple columns

@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 8 on at

Like (0)

Report

Re: String to Table with Multiple columns

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 147,161 Most Valuable Professional on at

Like (0)

Report

Re: String to Table with Multiple columns

@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 8 on at

Like (0)

Report

Re: String to Table with Multiple columns

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,
"{",
""
),
"}",
""
),
"\",
""
),
"",
""
),
"",
""
),
"""",
""
),
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"
)
)
)
Verified answer

WarrenBelz 147,161 Most Valuable Professional on at

Like (0)

Report
Re: String to Table with Multiple columns

@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, "{", "" ), "}", "" ), "\", "" ), "", "" ), "", "" ), """", "" ), 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