Forum Discussion
Help with how to process lists
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)?
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! 😀
3 Replies
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! 😀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
