Forum Discussion
Microsoft EmployeeFilter by multiple sets of column
Community Champion
i am just trying to understand what you are asking for.
You want to take the below 15 columns and be able to filter all of them at the same time? so combining all of this into one column via a pivot wouldn't work for you or would?
Microsoft EmployeeWhat I want to achieve is convert from this structure:
| Datetime | Customer1_value1 | Customer1_value2 | … | Customer1_value15 | Customer2_value1 | Customer2_value2 | … | Customer2_value15 | … | CustomerN_value1 | CustomerN_value2 | … | CustomerN_value15 |
| datetime1 | 0.229151652 | 0.155057721 | … | 0.324584312 | 0.110479662 | 0.16026891 | … | 0.090275502 | … | 0.967682291 | 0.493830084 | … | 0.48551334 |
| datetime2 | 0.547241 | 0.767585491 | … | 0.906074292 | 0.443983587 | 0.223704644 | … | 0.395398869 | … | 0.59960649 | 0.86645009 | … | 0.221637108 |
| datetime3 | 0.895628899 | 0.814316364 | … | 0.949635827 | 0.101984896 | 0.977557759 | … | 0.922149649 | … | 0.059627022 | 0.234168244 | … | 0.065165743 |
| … | … | … | … | … | … | … | … | … | … | … | … | … | … |
to this:
| Datetime | value1 | value2 | … | value15 | Customer |
| datetime1 | 0.229151652 | 0.155057721 | … | 0.324584312 | Customer1 |
| datetime1 | 0.110479662 | 0.16026891 | … | 0.090275502 | Customer2 |
| … | … | … | … | … | … |
| datetime1 | 0.967682291 | 0.493830084 | … | 0.48551334 | CustomerN |
| datetime2 | 0.547241 | 0.767585491 | … | 0.906074292 | Customer1 |
| datetime2 | 0.443983587 | 0.223704644 | … | 0.395398869 | Customer2 |
| … | … | … | … | … | … |
| datetime2 | 0.59960649 | 0.86645009 | … | 0.221637108 | CustomerN |
| datetime3 | 0.895628899 | 0.814316364 | … | 0.949635827 | Customer1 |
| datetime3 | 0.101984896 | 0.977557759 | … | 0.922149649 | Customer2 |
| … | … | … | … | … | … |
| datetime3 | 0.059627022 | 0.234168244 | … | 0.065165743 | CustomerN |
| … | … | … | … | … | … |
Sorry, I am trying to not overcomplicate things...
Thanks!
- sdjensen
Solution Sage
I 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.
- eldarkt
Thanks, 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!