cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
KOULE Frequent Visitor
Frequent Visitor

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

 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User III
Super User III

Re: Multiple data in one cell; how to plot or move to new table?

@KOULE 

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 @Nick_M's. Have a look at the attached file. First at the pre-processing in M and then the setting up of the chart.

Cheers 

View solution in original post

4 REPLIES 4
Super User IV
Super User IV

Re: Multiple data in one cell; how to plot or move to new table?

@KOULE see if attached helps, you can make changes as per your need.






Did I answer your question? Mark my post as a solution.

Proud to be a Datanaut! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





Super User I
Super User I

Re: Multiple data in one cell; how to plot or move to new table?

Maybe this is what you  had in mind?

Final Output.png

 

Most of that was done in Power Query, so please take a look at the applied steps in the attached file below.

Steps:

  1. Split the column with all the numbers, but into rows, not columns
  2. Group by your Column A, Aggregate by all All Data
  3. Add an index column to each subtable
  4. 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:Final PQ table.png

Can set a slicer on Column A, and will only show values in that column

 

File:

https://1drv.ms/u/s!Amqd8ArUSwDS0RQDXeea1Kf5UEbK

Super User III
Super User III

Re: Multiple data in one cell; how to plot or move to new table?

@KOULE 

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 @Nick_M's. Have a look at the attached file. First at the pre-processing in M and then the setting up of the chart.

Cheers 

View solution in original post

KOULE Frequent Visitor
Frequent Visitor

Re: Multiple data in one cell; how to plot or move to new table?

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.

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

January 2020 Community Highlights

January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January!

Top Solution Authors