Forum Discussion
Microsoft EmployeeFilter by multiple sets of column
Hi all, I have a data set like this:
| Customer1_value1 | Customer1_value2 | … | Customer1_value15 | Customer2_value1 | … | Customer2_value15 | … | CustomerN_value1 | … | CustomerN_value15 |
| 0.229151652 | 0.155057721 | … | 0.324584312 | 0.110479662 | … | 0.090275502 | … | 0.967682291 | … | 0.48551334 |
| 0.547241 | 0.767585491 | … | 0.906074292 | 0.443983587 | … | 0.395398869 | … | 0.59960649 | … | 0.221637108 |
| 0.895628899 | 0.814316364 | … | 0.949635827 | 0.101984896 | … | 0.922149649 | … | 0.059627022 | … | 0.065165743 |
| … | … | … | … | … | … | … | … | … | … | … |
So this is N sets of 15 columns.
Technically, N<20, but the main problem for me it's dynamic, N will change from time to time.
One set for every N value always contains 15 columns, this value is static.
All data types for different N values is the same (for example, Customer1_value2 and Customer2_value2 and CustomerN_value2 columns has the same data type)
What I wnat to achieve is to be able to filter all my visual by N (for example, with "slicer" visual).
Any help would be appreciated.
6 Replies
- vanessafvg
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?
eldarktWhat 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.
