How to separate a column of key:value pairs into new columns using direct query source

[jwarner1121]

I'm trying to separate a column of what looks like key:value pairs in a dictionary into separate columns. My problem is that I can only access the data through direct query. Is there any python or DAX code that I can use to accomplish this? I'm familiar with Python but never used it in Power BI. Below is a sample of the column that I'm working with (custom_fields). Each index row contains the same keys. I would like to have a table containing the column headers highlighted in yellow that I can use either as a lookup table or to create a relationship to another table by the index number. Thanks in advance!

[jwarner1121 · Accepted solution]

I did a little more searching in this forum and found a solution that I was able to use successfully. Here is the link:  https://community.powerbi.com/t5/Desktop/How-to-copy-a-specific-string-from-a-column-to-another/m-p/... 

and this is my column code for the first and last column:

Job # = 
var _job = "Job #"
var _jobLen = LEN(_job) + 4
var _jobMissing = ISERROR(FIND(_job, 'Custom Fields'[custom fields]))
var _jobPosition = IF(_jobMissing, BLANK(), FIND(_job, 'Custom Fields'[custom fields]) + _jobLen)
var _market = "Project Market"
var _jobEnd = IF(_jobMissing, BLANK(), FIND(_market, 'Custom Fields'[custom fields]) - 4)

return
    IF(_jobMissing, BLANK(), MID('Custom Fields'[custom fields], _jobPosition, _jobEnd - _jobPosition))

Estimator = 
var _estimator = "Estimator"
var _estimatorLen = LEN(_estimator) + 4
var _estimatorMissing = ISERROR(FIND(_estimator, 'Custom Fields'[custom fields]))
var _estimatorPosition = IF(_estimatorMissing, BLANK(), FIND(_estimator, 'Custom Fields'[custom fields]) + _estimatorLen)
var _stringEnd = FIND(UNICHAR(125), 'Custom Fields'[custom fields], _estimatorPosition)
var _estimatorEnd = IF(_estimatorMissing, BLANK(), _stringEnd - 1)

return
    IF(_estimatorMissing, BLANK(), MID('Custom Fields'[custom fields], _estimatorPosition, _estimatorEnd - _estimatorPosition))

[jwarner1121]

Thanks for your quick response. I'm not able to use Power Query as the data cannot be imported - only available via direct query. I'm looking for another option.

[amitchandak]

@jwarner1121 , first remove/replace { and } with empty string

Then split by delimiter first by comma (,)  into rows and then by => into columns 

Power Query Replace Value: https://youtu.be/hkZhZbR7Kmk

https://www.tutorialgateway.org/how-to-split-columns-in-power-bi/