Forum Discussion
Delimiter at each instance of an integer
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
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
11 Replies
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
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
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
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) _____________ 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 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}})
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
Thats sorted it! Thank you very much Xiaoxin
Laura
