Help with how to process lists

[Anonymous]

I currently have two tables,

Objects:

Timestamp ¦ Employee ¦ TaskID

16/8/2020          x            [list] {"fr5634-344"}

17/8/2020          y            [list] {"rg5623-933", "fr5634-344"}

The list contains Id's (in text format), and can have 0 or more Id's in them. 

I then have a criteria table that describe what each ID means:

ID                  ¦ Value(gbp)

rg5623-933     20

fr5634-344      60

---
Currently, I am having problems finding the total value(gbp) of a table. I tried unpacking and seperating with commas, but then lists with more than one ID do not relate to the criteria table.

What is the best way to count the total Value(gbp) of certain amounts of rows from the Objects table (in this example £140)? How can I count how many seperate ID's there are (in this example 3)? 

[lkalawski · Accepted solution]

Hi @Anonymous ,

In your case, it is best to split into next lines for records that have more than one item in the list.
To do this:
1.First clear the column of unnecessary characters with Replaced Value to achieve this:

2. Split a column with a separator: comma. Just do not divide into next columns, but into next rows

Right-click on the column name, select Split by delimiter, then in the new window select comma and in advanced options select into Rows. Additionally, turn off Quote Character.

Thanks to this, you will achieve division into appropriate values in the list. Now you just need to link this table to the dictionary table and you will get the results.

_______________
If I helped, please accept the solution and give kudos! 😀

[amitchandak]

@Anonymous , see like json data , find how to deal

https://www.youtube.com/watch?v=ipI6mrWLQKA
https://www.mssqltips.com/sqlservertip/4621/using-power-bi-with-json-data-sources-and-files/
https://zappysys.com/blog/howto-import-json-rest-api-power-bi/?gclid=EAIaIQobChMI7Za92YSi6wIVFSQrCh0vDgIgEAAYASAAEgLd5_D_BwE

[Anonymous]

This does not help. I have imported JSON just fine. This question Is not about JSON. I am asking how I can relate a collumn with a list of ID's, to another Table that has the properties of those ID's.