Forum Discussion
query data in several columns
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
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
5 Replies
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
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
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
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
It works! Thanks mahoneypat
