Split column by comma delimiter in some and no comma in others

[Anonymous]

Hello

I have a DirectQuery connection to a SQL server database.  I have a column that could have from 1 to 2 to 3, max, entries in them delimited by comma.  I need to separate the anwsers and then put them into one column.  But they can have a range of answers, meaning it can be:

Answer1

Answer1,Answer2

Answer1,Answer2,Answer3

It can be any types of these.  Hard to grab the first answer type becuase there is no comma to search off.  Please advise.  Thanks

[fhill]

See if this works!

Raw data matching example:

Select the Answer column and choose Advanced options under 'Split Coulmn"  You want ROWS instead of coulmns.

Final Result:

[Anonymous]

thanks fhill, I am not ble to get to the raw data, it is a direct query from SQL server.  Thanks for the idea.

[fhill]

See if this works using "New Table" command under 'Modeling".

Break your coulmn up into 3 coulns using...

Ans_1 = PATHITEM(SUBSTITUTE(Table2[Answer1],",","|"),1)

Ans_2 = PATHITEM(SUBSTITUTE(Table2[Answer1],",","|"),2)

Ans_3 = PATHITEM(SUBSTITUTE(Table2[Answer1],",","|"),3) 

NewTable = UNION(
SELECTCOLUMNS( Table2, "T",Table2[Ans_1],"Name",Table2[Name],"Date",Table2[Date]),
SELECTCOLUMNS(Table2, "T", Table2[Ans_2],"Name",Table2[Name],"Date",Table2[Date]),
SELECTCOLUMNS(Table2, "T", Table2[Ans_3],"Name",Table2[Name],"Date",Table2[Date])
)

[Anonymous]

Sorry fhill, I do not have the New Table option, since I am in DirectQuery.

Also shows error for the first step as PATHITEM is not able to be used in DirectQuery models.

Another great idea to try but not in my current mode.

[Anonymous]

I can et the one with one answer pulled out, I can get the second one with 2 answers pulled out, but I can not get the third.  I am having trouble with MID or RIGHT or SEARCH or FIND.  Once I get them pulled I am not certain how I am oing to get them all in one column.  Thanks in advance for any and all help.

[fhill]

....  In this mode, are you using SQL statments to pull the data from the database directly by chance...?

[Anonymous]

I am using the fields directly.  Not certain I know what you mean.  Sorry.

[Anonymous]

Trying to think of other ways around this.  Still with the DirectQuery pull, I was looking at SUBSTITUTE and thinking I can sub first , with one thing and second with something else and do a LEFT and search for it and go from there.  I was able to do a nested SUB as a test but when I try to add the last option of the function tells me SUB not in DirectQuery.  Is there a DirectQuery option for SUB or REPLACE?  Thanks

[fhill · Accepted solution]

See this post...  I think you're going to have to get someone to help you write your SQL input as a SQL Statement instead of just using the DirectQuery default option.  

https://community.powerbi.com/t5/Desktop/Create-a-query-to-select-data-from-a-table/td-p/26581

Building your own SQL Statement will give you more flexability in your aquisition of the data from the Database (resolving your commas issues).  You will just have to find a SQL programmer to assist you...

FOrrest

[Anonymous]

Thank you Forrest.  I am going to give this a look and see what I can try and figure out from here and leave the post open for now.  Thanks

[v-huizhn-msft]

Hi @Anonymous,

There are some limitations for Direct query Mode, you can see the limitations part in this article. It's good to try SQL statement to filter data from SQL Datebase. Please feel free to ask if you have any problem related to Power BI.

Best Regards,
Angelia

[fhill]

....  P.S.   I just tried inserting the 'PATHITHEM(SUBSITUTE... commands directly into the UNION(SELECTCOLUMNS( and that works too if you want to hit it in one blow...

FOrrest