Sunday, September 29, 2019

Einstein Analytics: Transpose data from columns to rows

In the table-1 below; Data-1, Data-2, and Data-3 are stored as individual columns.

Here is what we can get from Table-1

But, we can't have a total of A, B, and C as a single bar chart for easy comparison, the easiest option is to change the dataset into Table-2.

Solution: use dataflow to expand the rows and create new fields with combine values

- ceData1,ceData2,ceData3 contain 2 compute fields: Type and Value
   - Type_Data_1 = "Data-1"
   - Value_Data_1 = Data_1
   - same goes for ceData2 and ceData3

- In appendAllData, select "Allow disjoint schema"

- ceData also contain compute field Type and Value, and using Case in the formula

for Type_Data
  when Type_Data_1 is not null then "Data-1" 
  when Type_Data_2 is not null then "Data-2"
  when Type_Data_3 is not null then "Data-3"

for Value_Data
  when Type_Data_1 is not null then Data_1
  when Type_Data_2 is not null then Data_2
  when Type_Data_3 is not null then Data_3

Here our result:


  1. Great post, came in handy today was able to follow the method to transpose a revenue dataset that had monthly values column by column and not easily viewed as a time series.

    Question around the last compute 'ceData' using Cases, what is the purpose of this since the first 3 computes would have populated the type & values already?

    Thanks! C

  2. Awesome post! It helped me a lot while I was transposing a dataset with year and month columns! (I had 12 month columns and had to transpose into one as "Date")

    Your posts are always a big help, thank you so much!


Page-level ad