cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
GEN3Electric Frequent Visitor
Frequent 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

Accepted Solutions
blopez11 Established Member
Established Member

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

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

4 REPLIES 4
Highlighted
fathomson Frequent Visitor
Frequent Visitor

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

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

 

 

 

GEN3Electric Frequent Visitor
Frequent Visitor

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

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. 

blopez11 Established Member
Established Member

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

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

GEN3Electric Frequent Visitor
Frequent Visitor

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

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