Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
KOULE
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

@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 @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 

View solution in original post

4 REPLIES 4
KOULE
Frequent Visitor

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.

Anonymous
Not applicable

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

@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 @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 

parry2k
Super User
Super User

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



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.