Help separating single row of data into multiple rows

[skerbel]

Hi everyone:

I have a single row of data coming into Power BI that I would like to separate into multiple rows. 

The data is from a survey form and is formatted like this in the single row: 

Courtesy: 5 <br> Knowledge: 5 <br> Timeliness: 5 <br> Quality: 5 <br> Overall: 5 <br> Would you like someone to contact you regarding this survey?: No <br>

I know that I can use the transform data option in Power BI and split the data by delimiter, which in this case would be the <br> characters. However! Sometimes a user will not fill out every option in the survey, and if I split the results by the <br> delimiter, you will see some surveys that show the Timeliness option, for example, in the row that should normally be for the Knowledge response. 

I am relatively new to Power BI so I'm wondering if there's a different way to do this so that the fields and values are all properly aligned even if a response has a blank entry. 

Thank you for your time! 

Simon

[skerbel]

Hi Greg! Thank you for your prompt reply, I will give this a try and report back ASAP!

[Greg_Deckler]

@skerbel Try pasting this into a Blank query using Advanced Editor:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("zc8xC8IwEAXgv3J0diiISxcHR0ERBIfaISZHPLzm4C6p5N8LHSWTk9MbPni8N47dQYpmtDrADu6l77f+oWsiHJO8GUPEhl1pRqaEZg28FMeUW5XnBdUxN+QmhQNUKcD0QjCZURJCFvCSsvN5NcXoNFCKkJ9kYEUXrPsBTvLV102bn7/9xfzpAw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column", type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Column", Splitter.SplitTextByDelimiter(" <br> ", QuoteStyle.Csv), {"Column.1", "Column.2", "Column.3", "Column.4", "Column.5", "Column.6"}),
    #"Replaced Value" = Table.ReplaceValue(#"Split Column by Delimiter"," <br>","",Replacer.ReplaceText,{"Column.5", "Column.6"}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Replaced Value", {}, "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Columns",{"Attribute"}),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Removed Columns", "Value", Splitter.SplitTextByDelimiter(":", QuoteStyle.Csv), {"Value.1", "Value.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Value.1", type text}, {"Value.2", type text}})
in
    #"Changed Type1"

In words, split by delimiter on " <br> ". Replace values on last few columns of " <br>" to "". Unpivot the columns. Split the resulting column on colon :