Jump to content
We've recently updated our Privacy Statement, available here. ×

How to add data tables with multiple criteria


Cristin Xu

Recommended Posts

I have a table with data, company, products, and related sales volumes. I want to split them intofour columns. But it is not doable for company and products, as they are listed in two seprate rows. How can I insert the table to show all categories Additionally, I have to insert columns from other file to this tab, however, with only one condition can be matched.
Link to comment
Share on other sites

Here are the steps I followed to achieve the use case (might not be the best one).

1. When you import the excel file with raw data, you get the data preview dialog box. Select the Product and Company rows as 'Name' rows as shown in the screenshot below.

 

 

2. Go to Data >> Transform Data >> Select the Unpivot operation >> Insert >> Add the [Company, Products] column under 'Columns to pass through' >> Add the rest of the columns under 'Columns to transform' >> Click OK

3. Go to Column Properties and rename the [Company, Products] column as [Date].

4. Go to Data >> Add Calculated column with the expression: Substring([Category],1,1) and name the columns as [Company]

5. Again, go to Data >> Add Calculated column with the expression: Substring([Category],4,4) and name the columns as [Products]

6. Go to Properties (right-click table viz) >> Columns >> Keep only the required four columns: [Date],[Company],[Products], and [Value].

You can refer to the attached analysis file. I hope this will help.

Link to comment
Share on other sites

Thank you so much for your kind help. However, if I want to insert columns and match the related category, company and products will not display in the listed box. So how can I solve this issue.

 

I also can do by splitting colmumn, but again can't match the right column.

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...