Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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)?
Solved! Go to 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! 😀
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
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
105 | |
94 | |
75 | |
63 | |
62 |
User | Count |
---|---|
137 | |
105 | |
104 | |
80 | |
63 |