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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
GEN3Electric
Regular Visitor

Report uses this compression I don't know how to work with it.

I'm working with a business software that outputs reports with what I think is a row compression. The software will put multible peices of data into a cell and separate them by a commas.  

 

What is this called?

How do you work with it?

Is there an easy way to uncompress the information?

 

I shot a quick video example of what I'm looking at and what I think it should look like uncompressed. 

 

Thank you for the help.

1 ACCEPTED SOLUTION

If I'm understanding correctly, in the query editor, you can select the column, then in the Transform tab, select Split Column (by Delimiter) in the Text Column area

This should give you the option to split the column into multiple columns, then maybe you could use Pivot or Unpivot columns to move to their own rows

However without seeing what you are wanting, I'm only surmising what may help

View solution in original post

4 REPLIES 4
fathomson
Advocate II
Advocate II

Hi GEN3Electric,

 

It seems that the software outputs an xlsx version of the database.  Each tab in excel represents a column in the database. The numbers/id's are references to other tables. The 'multiple pieces of  data in a cell' represents a one-to-many relationship.

 

I am not an experienced excel user but maybe something like this works:

https://support.office.com/en-us/article/Create-a-relationship-between-tables-in-Excel-fe1b6be7-1d85...

 

 

 

That is close. I'm looking to expand the excell table so that each cell with many data points is moved to it's own row. Then I can mash it up like in the like you shared. 

If I'm understanding correctly, in the query editor, you can select the column, then in the Transform tab, select Split Column (by Delimiter) in the Text Column area

This should give you the option to split the column into multiple columns, then maybe you could use Pivot or Unpivot columns to move to their own rows

However without seeing what you are wanting, I'm only surmising what may help

That worked. Thank you. Here is the the solution. 

 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

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