Forum Discussion
Delimiter at each instance of an integer
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 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
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=TypeLmendes 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.