query data in several columns

[jürgs]

Hi all,

I have a column whose values are a list of elements (variable) comma separated. 

For example:

Column: Labels

row1: apple, pear

row2: apple, pear, strawberry

etc.

I separate these elements into several columns, so I have only 1 element per column. E.g.:

Column: Labels.1

row1: apple

row2: apple

Column: Labels.2

row1: pear

row2: pear

Column: Labels.3

row1: "blank"

row2: strawberry

Power BI automatically add the index number depending on how many elements I have in the list.

Sometimes this column will expand to 3 (until labels.3) and some other to X (until labels.x) depending on the nature of the data I import.

I am looking for a query (to be placed in a new column: "querry_label") in which I can query the value "strawberry" and it will look it up in the columns from: labels.1 until labels.X. 

As a result, I would have: - considering the above "dataset"

Column: "querry_label"

row1: blank

row2: strawberry

I have looked for a solution anywhere I could, but I was not successful. Therefore, I am hoping someone in this community could help me.

Any hint is highly appreciated.

Kind regards, jürgs

[jürgs]

It works! Thanks @mahoneypat 

[jürgs]

Thanks @mahoneypat , your solution worked 🙂

However now I have the following problem (derived from the expansion into new rows):

In column1:key I have several ids:

row1: 1

row2: 2

row3: 3

The column2 was the labels column described before (apple, strawberries, etc).

If I then expand the labels into new rows as you suggested before I get:

Colum1:key; Colum2:labels_expanded

row1: 1; apple

row2: 1; pear

row3: 2; apple

row4: 2; pear

row5: 3; strawberry

etc

If I then load the data and display the labels_expanded column, it will only show the last "label_expanded" value for the same key "id" value. For example:

Table visual:

headers: key; label_expanded; labels

row1: 1; pear; "apple,pear"

Would it be possible to display all the expanded labels for a certain id? So I could really use the slicer and say:

show me all the ids in which "apple" appear?

it should show me: key =1 and key = 2

with the current solution it shows me none as "apple" is not considered in the "labels_expanded" column.

Thanks again for your help.

jürgs

[Syndicate_Admin]

Not sure what you mean.  If you make a table with just the Key column and choose a value from a slicer with your new unpivoted label column, you should see only the keys that contain that one.  If you want a measure that shows you all the labels that contain the selected label, you can use a measure like this

AllLabels =
VAR label2 =
    CALCULATETABLE ( DISTINCT ( Labels[Label] ), ALL ( Labels[Label] ) )
RETURN
    CONCATENATEX ( label2, Labels[Label], ", " )

Pat

[mahoneypat]

Not sure what you mean.  If you make a table with just the Key column and choose a value from a slicer with your new unpivoted label column, you should see only the keys that contain that one.  If you want a measure that shows you all the labels that contain the selected label, you can use a measure like this

AllLabels =
VAR label2 =
    CALCULATETABLE ( DISTINCT ( Labels[Label] ), ALL ( Labels[Label] ) )
RETURN
    CONCATENATEX ( label2, Labels[Label], ", " )

Pat

[mahoneypat · Accepted solution]

What you are asking for is doable, but it would be simpler if you do the following:

1. Don't split your initial data into separate columns, and instead add a custom column using Text.Split([TextListColumn], ", ") to create a list of values

2. Click on the arrow at the top of the new column and choose "Expand to New Rows"

3.  Either Filter that new column for "Strawberry", or load the data and use a DAX measure and/or slicers to get your desired counts/analysis.

Pat