Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Solved! Go to Solution.
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
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?
Proud to be a Super User!
What 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!
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.
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!
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
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!
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
117 | |
101 | |
71 | |
61 |