Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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
Hi Greg! Thank you for your prompt reply, I will give this a try and report back ASAP!
@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 :
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
93 | |
87 | |
80 | |
69 | |
68 |
User | Count |
---|---|
226 | |
129 | |
119 | |
83 | |
77 |