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

Question

Hello :)&nbsp;&nbsp;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).&nbsp;&nbsp;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.&nbsp;&nbsp;&nbsp;I would also like to remove the "" as well. Thank you so much!&nbsp;

gazzer · Answer

That looks like JSON to me.&nbsp;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 · Answer

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 · Answer

Sorry, I did not think that through.&nbsp;First&nbsp;duplicate the original column and then do the Parse JSON function on the duplicate.&nbsp;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).&nbsp;This will let you choose the fields to extract from the tags.&nbsp;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 · Answer

Gazzer&nbsp;&nbsp;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&nbsp;any values in the duplicate column. Is there a way to fix that?&nbsp;

gazzer · Answer

I just tried a little experiment and I think it is that the empty fields are not truly empty.&nbsp;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.&nbsp;I also tried replacing with curly braces {} with nothing between and that worked as well.

Forum Discussion

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

7 Replies

Related content

Extract Data from sharepoint

Contain Word

DAX - Extract Date from TimeStamp (contain date & time)

check if value contains in another table

Visual calculation- Contain