Forum Discussion
Need suggestion for manipulating data
So we have data sources for survey data. All is good, enjoy working with Power BI and the community.
We have a problem when working with matrix quesions. Please see below. Qeustion 3 is a matrix question. Our provider puts all "sub"questions in one column. We could use text to columns in our excel sheet prior to bringing in to power bi, but as you can see in ID 3, sometimes the questions are out of order.
Any ideas?
| ID | Question 1 - Do you like trees | Question 2 - Do you like animals | Matrix Question 3 -When thiking about nature… |
| 1 | Yes | No | I like trees more than animals:Yes | I like animals the least:No | I never like nature:True |
| 2 | No | Yes | I like trees more than animals:No | I like animals the least:No | I never like nature:False |
| 3 | No | Yes | I never like nature:True | I like animals the least:No | I like trees more than animals:Yes |
Hi rnehrboss,
I'd like to suggest you extract the text values to list and split the answer and value, then you can use them to create a matrix to show the result as your expected.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUYpMLQaSfvlAwlMhJzM7VaGkKDW1WCE3vwjIzEjMU0jMy8xNzCm2AqpUqFGAKoIKAlWkKuSkJhaXWPnlg2XzUstSiyBq8hJLSotSrUKKSlOVYnWilYxgFkHsJGAd1DxSbXMDqoNYZ4xuHXanEWELoWBRio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, #"Question 1 - Do you like trees" = _t, #"Question 2 - Do you like animals" = _t, #"Matrix Question 3 -When thiking about nature…" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Question 1 - Do you like trees", type text}, {"Question 2 - Do you like animals", type text}, {"Matrix Question 3 -When thiking about nature…", type text}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each List.Transform(Text.Split([#"Matrix Question 3 -When thiking about nature…"],"|"),each Text.Trim(_))), #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"), #"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"Matrix Question 3 -When thiking about nature…"}), #"Split Column by Delimiter" = Table.SplitColumn(#"Removed Columns", "Custom", Splitter.SplitTextByDelimiter(":", QuoteStyle.Csv), {"Question", "Answer"}) in #"Split Column by Delimiter"Regards,
Xiaoxin Sheng
6 Replies
rnehrboss Can you split that 3rd column out on "|" character? Sorry, what is your expected output?
Not sure what "A" is ... we can split by "|" but example ID 3 Question 3 is out of order.
Thanks.. here is the input with expected output. We can easily split by "|" but you see question 3 for ID 3 is out of order.
Current Data ID Question 1 - Do you like trees Question 2 - Do you like animals Matrix Question 3 -When thiking about nature… 1 Yes No I like trees more than animals:Yes | I like animals the least:No | I never like nature:True 2 No Yes I like trees more than animals:No | I like animals the least:No | I never like nature:False 3 No Yes I never like nature:True | I like animals the least:No | I like trees more than animals:Yes Expected Data ID Question 1 - Do you like trees Question 2 - Do you like animals Matrix Question 3 -When thiking about nature… 1 Yes No I like trees more than animals:Yes I like animals the least:No I never like nature:True 2 No Yes I like trees more than animals:No I like animals the least:No I never like nature:False 3 No Yes I like trees more than animals:Yes I like animals the least:No I never like nature:True Accidentely replied on the spanish board.
We can split by "|" but you see ID 3 question 3 is out of order. Somehow we need Column D to split and line up the resulting questions in the correct column.
Thanks for the quick replies.
Here is input and expected output:
Current Data ID Question 1 - Do you like trees Question 2 - Do you like animals Matrix Question 3 -When thiking about nature… 1 Yes No I like trees more than animals:Yes | I like animals the least:No | I never like nature:True 2 No Yes I like trees more than animals:No | I like animals the least:No | I never like nature:False 3 No Yes I never like nature:True | I like animals the least:No | I like trees more than animals:Yes Expected Data ID Question 1 - Do you like trees Question 2 - Do you like animals Matrix Question 3 -When thiking about nature… 1 Yes No I like trees more than animals:Yes I like animals the least:No I never like nature:True 2 No Yes I like trees more than animals:No I like animals the least:No I never like nature:False 3 No Yes I like trees more than animals:Yes I like animals the least:No I never like nature:True Hi rnehrboss,
I'd like to suggest you extract the text values to list and split the answer and value, then you can use them to create a matrix to show the result as your expected.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUYpMLQaSfvlAwlMhJzM7VaGkKDW1WCE3vwjIzEjMU0jMy8xNzCm2AqpUqFGAKoIKAlWkKuSkJhaXWPnlg2XzUstSiyBq8hJLSotSrUKKSlOVYnWilYxgFkHsJGAd1DxSbXMDqoNYZ4xuHXanEWELoWBRio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, #"Question 1 - Do you like trees" = _t, #"Question 2 - Do you like animals" = _t, #"Matrix Question 3 -When thiking about nature…" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Question 1 - Do you like trees", type text}, {"Question 2 - Do you like animals", type text}, {"Matrix Question 3 -When thiking about nature…", type text}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each List.Transform(Text.Split([#"Matrix Question 3 -When thiking about nature…"],"|"),each Text.Trim(_))), #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"), #"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"Matrix Question 3 -When thiking about nature…"}), #"Split Column by Delimiter" = Table.SplitColumn(#"Removed Columns", "Custom", Splitter.SplitTextByDelimiter(":", QuoteStyle.Csv), {"Question", "Answer"}) in #"Split Column by Delimiter"Regards,
Xiaoxin Sheng
