Create New Column and Extract Values from another column containing list of tags

[Bepachec]

Hello 🙂 

I have a column "Tags" where each cell going down the column has a long dictionary of items if you will. An example cell would look like: {"Cost Center": "0000", "Application": "PowerBI", "Environment": "DEV"}. However each cell has a different number of tags (one without a cost center, ones with a bunch of junk text, etc). 

I have a list of tags that I am looking for in each row and want to create a column based on that tag. For example I want to add a Business Cost Center Column and extract all the values that come after the "Business Cost Center": into that new column respectively. Then I would like to do the same thing for about six or seven other tags. I am not sure how to approach this as the Tags column contains a multitude of delimeters and values. 

I would also like to remove the "" as well. Thank you so much! 

[v-juanli-msft]

Hi @Bepachec

Here is a way

Split Column By Delimiter

Then rename the column and apply the same steps to other columns

Best Regards

Maggie

[Gazzer]

That looks like JSON to me.

Highlight the column and do Transform / Parse / JSON and (if I'm right!) you should get a column for each data-part. In the case of your example, three columns, one each for Cost Center, Application and Environment.

[Bepachec]

@GazzerThanks for your reply! So I tried that and in the original Tags column now the values are either Error or Record. 

Here is the Error Message: DataFormat.Error: We reached the end of the buffer.

Where would I find the new columns if it did work for the others? 

Thank you! 

[Gazzer]

Sorry, I did not think that through.

First duplicate the original column and then do the Parse JSON function on the duplicate. 

This should create a column populated with "Record" (not sure about the error part of this, but one step at a time) in it. You need to click the little button in the header, to the right of the name (like two little arrows).

This will let you choose the fields to extract from the tags.

If you could post a couple of the tags for those that fail, maybe we can work out why they error (assuming there is nothing sensitive in the data, of course).

[Bepachec]

@Gazzer 

Thanks for that! So I did that and it looked like it parsed it accordingly. However, the errors are coming from those that do not have any values in the duplicate column. Is there a way to fix that? 

[Gazzer]

I just tried a little experiment and I think it is that the empty fields are not truly empty.

Immediately after duplicating the column, insert a new step to Replace Values. Put nothing in the first box and then type null in the second one. No quotes or anything, just the word null in lowercase.

I also tried replacing with curly braces {} with nothing between and that worked as well.

[Bepachec]

@Gazzer

Thanks for that - It looks like the {} fixed that issue. Now when I click the little icon to add the columns i get a message that says limit of 1000 rows scanned complete. However, the columns I need are not in that list.