How to add data tables with multiple criteria

[Cristin Xu]

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.

[Shashank Gurnalkar]

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.

[Cristin Xu]

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.

[Shashank Gurnalkar]

An example of this use case would be helpful to understand it better. I am not sure from where you are matching the columns. 

[Cristin Xu]

Please refer to the attachment. Thank you again for your kind help!