Forum Discussion
Most efficient way to split this column and pivot it
What is the most efficient way to split this column?
I want a separate column for the text in between two quotation marks.
Sample data in 1 cell:
{"DynamicProperties":{"DateCreation":"11172022","BankID":"apple","BatchID":"167929","Y_InvoiceID":"734950","Y_TotalNumberofInvoice":"5","Y_TotalAmount":"11494.40","Y_FundingEntity":"orange","Y_CompanyName":"McDonald's","Y_InvoiceDate":"10312022 00:00:00","Y_GrossAmount":"899.10","YNetAmount":"782.00","YExternalSource":"INV123456","Y_OrgCode":"apc","Y_TotalTaxAmount":117.1,"Y_StreetAddress":"150 Sandy Avenue 6th Floor","Y_PostalCode":"H5G 3N1","InvoicePath":"F:\\003\\CInvoice\\17-11-2022\\167929\\167929_258499.pdf","BatchFileGoogleDrivePath":"ohio\\Payable Register Report\\2022-11\\17\\invoice.pdf"}}
For example: end results should look something like this
| Column 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 |
| DateCreation | 11172022 | BankID | apple | BatchID | 167929 | Y_InvoiceID | 734950 | Y_TotalNumberofInvoice | 5 |
Then I would like to pivot these into:
| Date Creation | BankID | BatchID | Y_InvoiceID | Y_TotalNumberofInvoice |
| 11172022 | apple | 167929 | 734950 | 5 |
I find that Pivoting is taking a long time to load, is there a way to make it faster?
Note that I have more than 50k rows.
Thank you in advance!