Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
skerbel
New Member

Help separating single row of data into multiple rows

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

2 REPLIES 2
skerbel
New Member

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

Greg_Deckler
Super User
Super User

@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 :


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.