Forum Discussion
Multiple data in one cell; how to plot or move to new table?
Dear PBI community,
I would like your help with handling multiple data in one row of my table.
Each row of this column (colA) contains a vector of numbers. The size of vector may vary between different rows of this column, as you see in the following example.
What I want to do is create simple line plot with the numbers of this vector, but I am not allowed as PBI recognizes only the rows and columns of the outer table. In addition, this vecotr is recognized as text and if I change it to number the result is an infinite value.
Then I thought to export this vector in a new table within power bi and then use this new table for my plot, after having also transposed it. Thing is I don't know if this is possible and how to do it, as the dynamic length of the vector paused additional difficulty.
Initial Table
colA colB
1 1,2,3,2,1,2
2 2,3,3,3
3 1,3,2,2,2,2,2,2
...
Desired new Table (table will only contain 1 column and will dynamically change based on which row of the initial table the user has selected through a relevant slicer)
i.e.
when row1 of Initial Table is selected
1
2
3
2
1
2
when row 2 of initial Table is selected
2
3
3
3
when ro3 of initial Table is selected
1
3
2
2
2
2
2
2
4 Replies
Maybe this is what you had in mind?
Most of that was done in Power Query, so please take a look at the applied steps in the attached file below.
Steps:
- Split the column with all the numbers, but into rows, not columns
- Group by your Column A, Aggregate by all All Data
- Add an index column to each subtable
- Add a column to each subtable to combine the index with your column A, that is what I called "Level"
Table.AddColumn( [Custom],"Level", each Text.Combine( { Text.From([A]), Text.From([Index]) } , "." ) )- Remove misc columns and expand the table
- Set the data types
- Final Table:
Can set a slicer on Column A, and will only show values in that column
File:
- AlB
Community Champion
I see you have good answers already but since I'd prepared the answer already, I will post it anyway in case it is of any help. My approach is similar to Anonymous's. Have a look at the attached file. First at the pre-processing in M and then the setting up of the chart.
Cheers
Hello guys,
I cannot express how much grateful I am!
The reply was to the point and I applies your solution to my case. Expecially the attached files guided me through the complex queries as I was not familiar with this side of Power BI.
Thank you,
You can consider it solved.
