cancel
Showing results for
Did you mean:
Frequent Visitor

## Filter 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.

1 ACCEPTED SOLUTION

Accepted Solutions
Solution Sage

## Re: Filter by multiple sets of column

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
6 REPLIES 6
Super User I

## Re: Filter by multiple sets of column

@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?

Proud a to be a Datanaut!
Frequent Visitor

## Re: Filter by multiple sets of column

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!

Solution Sage

## Re: Filter by multiple sets of column

I guess you could try something like this:

1. Open the Query Editor

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
Frequent Visitor

## Re: Filter by multiple sets of column

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...

Solution Sage

## Re: Filter by multiple sets of column

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
Frequent Visitor

## Re: Filter by multiple sets of 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!

Announcements

#### New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

#### ‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

#### Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

#### Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

#### Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors