Forum

Dropdown list with ...
 
Notifications
Clear all

Dropdown list with multiple selections not splitting

6 Posts
2 Users
0 Reactions
151 Views
(@mrdamo)
Posts: 5
Active Member
Topic starter
 

Hi all, 

I have a dropdown list with multiple choices for an entire column. When I select more than 1 option, queries aren't performing by extracting the selected data to their corresponding tables.

How do I go about getting the queries to separate data?

 

Thank you

 
Posted : 29/04/2021 11:59 pm
(@mynda)
Posts: 4761
Member Admin
 

Hi Damian,

Can you please supply an example file so we can see what you mean and try to reproduce the problem?

Mynda

 
Posted : 01/05/2021 5:20 am
(@mrdamo)
Posts: 5
Active Member
Topic starter
 

Hi Mynda, 

I've attached a testing sheet to look at.

On the main data tab, in column A is where my dropdown list is placed. Only the row containing TIPT is being exported to the TIPT tab.

 

Thanks

Damian

 
Posted : 05/05/2021 7:59 pm
(@mynda)
Posts: 4761
Member Admin
 

Hi Damian, 

The file didn't get uploaded. Please try again and be sure to click the 'start upload' button after selecting your file and wait for the green text notification below the attachments box to indicate uploading has completed.

Mynda

 
Posted : 05/05/2021 8:13 pm
(@mrdamo)
Posts: 5
Active Member
Topic starter
 

Take #2, sorry.

 
Posted : 05/05/2021 9:48 pm
(@mynda)
Posts: 4761
Member Admin
 

Hi Damian,

You have filtered the column in the TIPT query for rows that 'equal' TIPT. Power Query is only finding exact matches, not any instance of TIPT along with other text. If you change the filter to 'contains' TIPT, then you get all the rows.

I must say, entering multiple values like this in a single cell is not how Excel is intended to be used. You will most likely come across other roadblocks when formatting your data like this. Ideally you should be structuring your data in a tabular format.

To fix the layout, you can use the Split Column by > Delimiter with the #(cr) custom delimiter and choose 'rows' under advanced options. This will create a separate row for each value selected in the drop down list in column A.

Mynda

 
Posted : 06/05/2021 7:52 am
Share: