Retrive text from other column in direct query

[TaufikMaggangka]

Hi everyone,

I need help about dax in directquery mode. 

Basicly, I want to retrive value from this column covidSymptoms to new column, as follow:

CovidSymptoms

fever lossofsmell cough

cough fever

lossofsmell cough

fever lossofsmell

I want to give space and separator,  and I hope the new column or costum table should be like this:

CovidSymptoms

fever, loss of smell, cough

cough, fever

lossofsmell, cough

fever, loss of smell

Stress about this, I try a lot of different ways such as using text.combines, text.contains with if statement but doesnt work in directquery mode.  Your prompt response will be appriciated.

[amitchandak · Accepted solution]

@TaufikMaggangka , You might able to get complex logic work at the column level, You have to create a measure. You can use search or find or containsstring

[TaufikMaggangka · Accepted solution]

Hi @v-yalanwu-msft , 
Thank you so much. This is give me a clue.
but I have another issues since I also want replace value of "fever" to "Fever", cough to "Cough"(basically I want to first alfabet is Capital) not only loss of smell will be replace.

I try  your sollustion, like this, but doesnt work. your future help will be appriciated.

Measure = var _replace=SUBSTITUTE(MAX([CovidSymptoms])," ",",")
return SUBSTITUTE(_replace,"lossofsmel","loss of smel")
return SUBSTITUTE(_replace,"fever","Fever")
return SUBSTITUTE(_replace,"cough","Cough")

[aj1973]

Hi @TaufikMaggangka 

It is good to know that there is some limitations in using DQ Mode.

Your model might fall into one of them...

Maybe a Measure with DAX can do the work to see it into a visual, but I don't think that adding a Calculated column is possible.

[v-yalanwu-msft]

Hi, @TaufikMaggangka ;

If you want to achieve it in power query ,you could Right click and use Replace Values, such as:

The final output is shown below:

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[TaufikMaggangka]

Thank you for trying to help, but this is not working in "Direct Query mode"

[v-yalanwu-msft]

Hi, @TaufikMaggangka ;

If your .pbix is  "Direct Query mode" , you should use dax (create a measure )as follows:

Measure = var _replace=SUBSTITUTE(MAX([CovidSymptoms])," ",",")
return SUBSTITUTE(_replace,"lossofsmel","loss of smel")

he final output is shown below:

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[TaufikMaggangka · Accepted solution]

Hi @v-yalanwu-msft , 
Thank you so much. This is give me a clue.
but I have another issues since I also want replace value of "fever" to "Fever", cough to "Cough"(basically I want to first alfabet is Capital) not only loss of smell will be replace.

I try  your sollustion, like this, but doesnt work. your future help will be appriciated.

Measure = var _replace=SUBSTITUTE(MAX([CovidSymptoms])," ",",")
return SUBSTITUTE(_replace,"lossofsmel","loss of smel")
return SUBSTITUTE(_replace,"fever","Fever")
return SUBSTITUTE(_replace,"cough","Cough")

[v-yalanwu-msft · Accepted solution]

Hi, @TaufikMaggangka ;

You could change measure as follows:

Measure = var _a=SUBSTITUTE(MAX([CovidSymptoms])," ",",")
var _b=SUBSTITUTE(_a,"lossofsmel","loss of smel")
var _c=SUBSTITUTE(_b,"fever","Fever")
return SUBSTITUTE(_c,"cough","Cough")

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[TaufikMaggangka]

Thank you so much. this is a sollution.

[TaufikMaggangka]

• Thanks @amitchandak  For your clue. Would you like to give me an exampel measure based on my table above.  I try to create measure but not working since this also need to combine the the texts. 

[amitchandak · Accepted solution]

@TaufikMaggangka , You might able to get complex logic work at the column level, You have to create a measure. You can use search or find or containsstring