Forum Discussion

Savanna2023's avatar
Savanna2023
New Member
3 years ago

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 12345678910
DateCreation11172022BankIDappleBatchID167929Y_InvoiceID734950Y_TotalNumberofInvoice5

 

Then I would like to pivot these into:

Date CreationBankIDBatchIDY_InvoiceIDY_TotalNumberofInvoice
11172022apple1679297349505

 

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!

No RepliesBe the first to reply