Delimiter at each instance of an integer

[Lmendes]

Hi

I have some concatenated data i.e.

1=Married 2=Single 3=In a sexual relationship 4=Widowed 5=Divorced / Separated 6= Cohabiting

I want to split out into seperate columns. I want to split it out at each instance of the integer - however, the concatenated data is a mix of text & integers. I have tried the below but i get an error message; 

We cannot convert a value of type List to type Text.
Details:
Value=List
Type=Type

= Table.SplitColumn(#"Removed Blank Rows", "Answers", Splitter.SplitTextByDelimiter({"0".."9"}, QuoteStyle.Csv), {"Answers.1", "Answers.2"})

Please help!! An alternative way to do this would be to split by = and -1 from the text length, but I dont know how to do this in M language

[v-shex-msft · Accepted solution]

Hi @Lmendes,

You can refer to below sample to transform QA table in query editor and use dax formula to lookup related answers.

Regards,

Xiaoxin Sheng

[Lmendes]

Thats sorted it! Thank you very much Xiaoxin

Laura

[Anonymous]

@Lmendes if i am not mistaken you will have one of the value as a response i.e. 1=Married or you will 2=Single etc...

could you plz post a column with 3 rows of sample date in an excel file(upload to dropbox or google drive and post link here) to clarify the question

[Anonymous]

@Lmendes try this 

1) Split based on space

   = Table.SplitColumn(Source, "Answer", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Answer.1", "Answer.2", "Answer.3", "Answer.4", "Answer.5", "Answer.6", "Answer.7", "Answer.8"})

2) Split By "="

    = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Answer.1", Int64.Type}, {"Answer.2", type text}})

[Lmendes]

Thanks for the suggested solution - unfortunately spaces exist in between some of the answers so it doesnt quite work.

I cannot attach the original file as it contains sensitive data but i have created a dummy file; here

Thank you

Laura

[Lmendes]

I hope this helps illustrate the issue a bit better;

So i am starting with data like this;

Question	Answer
Source of information	1=Radio 2=Church 3=Community Groups 4=Community awareness meetings 5=Other
Sex	1=Female 2=Male
Occupation	1=None   2=Formal Employment 3= Farmer 4=Business   5=Student 6= Other (specify) _____________

And i want it to look like this - after i unpivot columns and split by delimiter "="

Source of information	Answer1	1	Radio
Source of information	Answer2	2	Church
Source of information	Answer3	3	Community Groups
Source of information	Answer4	4	Community awareness meetings
Source of information	Answer5	5	Other
Sex	Answer1	1	Female
Sex	Answer2	2	Male
Occupation	Answer1	1	None
Occupation	Answer2	2	Formal Employment
Occupation	Answer3	3	Farmer
Occupation	Answer4	4	Business
Occupation	Answer5	5	Student
Occupation	Answer6	6	Other (specify) _____________

[Anonymous]

@Lmendes are the number of answers in a column fixed?

Just curious as to why would have all the options in the answer columns, wouldn't you have just 1 ans i.e. for source of information you will have 1=radio or 2=church etc.

If thats the case the solution is pretty simple

=Text.Select([data],{"0".."9"})

If the format is like you say, split the data based on spaces and then pivot the answers and use my method to filter numbers

[Lmendes]

Hi

Thanks for your help so far. I tried your solution but again i get this error (because the data is a mix of text & Int64). I need to keep both the text and the numbers as i need to know what each number relates to for each question.

Thank you

Laura

Expression.Error: We cannot convert a value of type List to type Text.
Details:
    Value=List
    Type=Type

[Anonymous]

@Lmendes It would make it lot easier if you can provide more details by sharing the power bi file you created. Else it is like shooting in the dark trying to guess where you are failing.

[Anonymous]

@Lmendes couldn't you split it by the "=" in there? also if the numbers are not needed replace them all by blank or some other character

[Lmendes]

thank you for the quick response! Unfortunately I do need the number as I will split out the numbers and use them as a score

Luara