Forum Discussion
How to Capture Individual Numbers in Dynamic Arrays per Cell
Good day,
I would like to ask if Power BI can capture the individual IDs in an entry with a dynamic count of ID values in an entry:
| Operator | Shift |
| 1 | 1 |
| 1,2 | 2 |
| 1,3,5 | 1 |
| 2 | 2 |
| 5,4,6,7,8 | 1 |
| 3,1,4,2 | 2 |
The operator table has a varying number of values inside a cell that need to be individually checked and captured. This is because each number represents a specific name. For example,
| OperatorID | OperatorFirstName | OperatorLastName |
| 1 | Reggie | Andrews |
| 2 | Donald | Banks |
| 3 | John | Ignatius |
| 4 | Peter | Jenkins |
| 5 | Andrew | Ports |
| 6 | David | Snow |
| 7 | Michael | Anders |
| 8 | Matthew | Lewis |
The end goal is to capture the operator names and number of operators on a given shift.
Thank you,
Nico
4 Replies
nidquival , One option it split the first column of the first table into rows in power query
https://www.tutorialgateway.org/how-to-split-columns-in-power-bi/
amitchandak, can this be done automatically using power query? The values will be coming from a database so the transactions will be constantly appended.
Hi nidquival ,
Not clear about the expected result you want. Can you provide the expected result?
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
I tried splitting the values, but being in DirectQuery mode, I was unable to do so. What we did instead was we separated the Operators into multiple tables. Here is the schema of the tables:
The mainID of the Main table has active one-to-one connections with the different Operator[x] tables. As such, I cannot create active many-to-one connections from the different Operator[x] tables using the OperatorID to the Operator table. Currently, the relationships are set to inactive, and I am supposed to filter out the name of the specifically selected operator.
Is there a way by DAX for me to filter the said table as I am using Directquery?
Thanks,
Nico
