Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
eldarkt
Employee
Employee

Filter by multiple sets of column

Hi all, I have a data set like this:

 

Customer1_value1Customer1_value2Customer1_value15Customer2_value1Customer2_value15CustomerN_value1CustomerN_value15
0.2291516520.1550577210.3245843120.1104796620.0902755020.9676822910.48551334
0.5472410.7675854910.9060742920.4439835870.3953988690.599606490.221637108
0.8956288990.8143163640.9496358270.1019848960.9221496490.0596270220.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.

1 ACCEPTED 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

/sdjensen

View solution in original post

6 REPLIES 6
vanessafvg
Super User
Super User

@eldarkt

 

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? 





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




What I want to achieve is convert from this structure:

DatetimeCustomer1_value1Customer1_value2Customer1_value15Customer2_value1Customer2_value2Customer2_value15CustomerN_value1CustomerN_value2CustomerN_value15
datetime10.2291516520.1550577210.3245843120.1104796620.160268910.0902755020.9676822910.4938300840.48551334
datetime20.5472410.7675854910.9060742920.4439835870.2237046440.3953988690.599606490.866450090.221637108
datetime30.8956288990.8143163640.9496358270.1019848960.9775577590.9221496490.0596270220.2341682440.065165743

 

 

to this:


Datetimevalue1value2value15Customer
datetime10.2291516520.1550577210.324584312Customer1
datetime10.1104796620.160268910.090275502Customer2
datetime10.9676822910.4938300840.48551334CustomerN
datetime20.5472410.7675854910.906074292Customer1
datetime20.4439835870.2237046440.395398869Customer2
datetime20.599606490.866450090.221637108CustomerN
datetime30.8956288990.8143163640.949635827Customer1
datetime30.1019848960.9775577590.922149649Customer2
datetime30.0596270220.2341682440.065165743CustomerN

 

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.

/sdjensen

Thanks, sdjensen!

Now I have this:

DatetimeAttribute.1Attribute.2Value
datetime1Customer1_value10.229151652
datetime1Customer1_value20.155057721
datetime1
datetime1Customer1_value150.324584312
datetime1Customer2_value10.110479662
datetime1Customer2_value20.16026891
datetime1
datetime1Customer2_value150.090275502
datetime1CustomerN_value10.967682291
datetime1CustomerN_value20.493830084
datetime1
datetime1CustomerN_value150.48551334
datetime2Customer1_value10.547241
datetime2Customer1_value20.767585491
datetime2
datetime2Customer1_value150.906074292
datetime2Customer2_value10.443983587
datetime2Customer2_value20.223704644
datetime2
datetime2Customer2_value150.395398869
datetime2CustomerN_value10.59960649
datetime2CustomerN_value20.86645009
datetime2
datetime2CustomerN_value150.221637108
datetime3Customer1_value10.895628899
datetime3Customer1_value20.814316364
datetime3
datetime3Customer1_value150.949635827
datetime3Customer2_value10.101984896
datetime3Customer2_value20.977557759
datetime3
datetime3Customer2_value150.922149649
datetime3CustomerN_value10.059627022
datetime3CustomerN_value20.234168244
datetime3
datetime3CustomerN_value150.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

/sdjensen

 

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) :

pivot.jpg
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! Smiley Happy

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.