Forum Discussion
Microsoft EmployeeFilter by multiple sets of column
Solution SageI guess you could try something like this:
1. Open the Query Editor
2. Select the Query that loads your table
3. Select all the columns besides your values columns
4. On the Transform tab open the drop down menu on 'Unpivot Colomns' and select 'Unpivot other columns'
5. Select your column with the values Customer1_value1, Customer1_Value2...CustomerN_ValueN and use the Split Column By delimiter (on the Home tab) to split the column into 2 column. Use '_' to split the column
6. Detele the column with the values Value1, Value2...ValueN (if you don't need it)
7. Rename columns to what ever you want.
Microsoft EmployeeThanks, sdjensen!
Now I have this:
| Datetime | Attribute.1 | Attribute.2 | Value |
| datetime1 | Customer1 | _value1 | 0.229151652 |
| datetime1 | Customer1 | _value2 | 0.155057721 |
| datetime1 | … | … | … |
| datetime1 | Customer1 | _value15 | 0.324584312 |
| datetime1 | Customer2 | _value1 | 0.110479662 |
| datetime1 | Customer2 | _value2 | 0.16026891 |
| datetime1 | … | … | … |
| datetime1 | Customer2 | _value15 | 0.090275502 |
| datetime1 | CustomerN | _value1 | 0.967682291 |
| datetime1 | CustomerN | _value2 | 0.493830084 |
| datetime1 | … | … | … |
| datetime1 | CustomerN | _value15 | 0.48551334 |
| datetime2 | Customer1 | _value1 | 0.547241 |
| datetime2 | Customer1 | _value2 | 0.767585491 |
| datetime2 | … | … | … |
| datetime2 | Customer1 | _value15 | 0.906074292 |
| datetime2 | Customer2 | _value1 | 0.443983587 |
| datetime2 | Customer2 | _value2 | 0.223704644 |
| datetime2 | … | … | … |
| datetime2 | Customer2 | _value15 | 0.395398869 |
| datetime2 | CustomerN | _value1 | 0.59960649 |
| datetime2 | CustomerN | _value2 | 0.86645009 |
| datetime2 | … | … | … |
| datetime2 | CustomerN | _value15 | 0.221637108 |
| datetime3 | Customer1 | _value1 | 0.895628899 |
| datetime3 | Customer1 | _value2 | 0.814316364 |
| datetime3 | … | … | … |
| datetime3 | Customer1 | _value15 | 0.949635827 |
| datetime3 | Customer2 | _value1 | 0.101984896 |
| datetime3 | Customer2 | _value2 | 0.977557759 |
| datetime3 | … | … | … |
| datetime3 | Customer2 | _value15 | 0.922149649 |
| datetime3 | CustomerN | _value1 | 0.059627022 |
| datetime3 | CustomerN | _value2 | 0.234168244 |
| datetime3 | … | … | … |
| datetime3 | CustomerN | _value15 | 0.065165743 |
| … | … | … | … |
Do you know is it possible to convert it to structure described (under "to this:") in my previous post?
I tried "pivot", it didn't work...
Thanks again for your help!
- sdjensen
Hi eldarkt,
This approach should do the trick
1. select the column 'Attribute.2'
2. Select 'Pivot Column' on the Transform tab
3. In the pop-up select 'Value' as Values Column
- eldarkt
Many thanks, sdjensen, it works!
My mistake when I tried pivot was I didn't turn off aggregation here (it was "count(all)" by default) :
and it showed "1" in each field instead of source values.
I changed it to "Don't Aggregate" and it did the trick.
What a wonderful forum it is! :smileyhappy: