Forum Discussion
How can PowerBI read a multiple answer, pipe-delimited survey data from Excel?
Hi there! I am currently working with survey data and above is an example of how my data is collected. There are about 10 mutiple response questions, and a few have potentially 7 different options. I am wanting to graph the response data. At first, I spilt the column by the delimiter and then I unpivoted the columns. However, my data got extremely large, extremely fast. Is there any other way here that PowerBI can read the data without having to spilt and unpivot the data? Thanks so much fr your help!!
Hi Anonymous ,
Here's how I would transform your data for reporting efficiency:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("tZDBDsIwDEP/Jef9UbVDWsLGFpqqSUGV8vGUIXHgCtws23qyHAJssmaYQI1wdyyFydUq3iPV2kcQUXeyiMx+E2bqhzyLvLzUKl/yMoonSr617NgWVyowTwESKvU3Pa1P5g/xOir9Y3zEJNlNrmjy/fwq/J9z5gc=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [name = _t, favFood = _t, favSport = _t, favMonth = _t]), unpivOthCols = Table.UnpivotOtherColumns(Source, {"name"}, "question", "Value"), addSplitResponses = Table.AddColumn(unpivOthCols, "response", each Text.Split([Value], "|")), expandSplitResponses = Table.ExpandListColumn(addSplitResponses, "response") in expandSplitResponsesThis creates a very long, but narrow, table - perfect for Power BI and reporting:
It may seem that your data getting to thousands/millions/billions of rows is a problem, but it's really not (unless you're using Direct Query, but probably out of scope here).
With this structure, you can create a simple COUNTROWS(tableName) measure, then use combinations of [name], [question], and [response] with this measure to create very fast and responsive visuals, even over millions/billions of rows.
Pete
2 Replies
- BA_Pete
Super User
Hi Anonymous ,
Here's how I would transform your data for reporting efficiency:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("tZDBDsIwDEP/Jef9UbVDWsLGFpqqSUGV8vGUIXHgCtws23qyHAJssmaYQI1wdyyFydUq3iPV2kcQUXeyiMx+E2bqhzyLvLzUKl/yMoonSr617NgWVyowTwESKvU3Pa1P5g/xOir9Y3zEJNlNrmjy/fwq/J9z5gc=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [name = _t, favFood = _t, favSport = _t, favMonth = _t]), unpivOthCols = Table.UnpivotOtherColumns(Source, {"name"}, "question", "Value"), addSplitResponses = Table.AddColumn(unpivOthCols, "response", each Text.Split([Value], "|")), expandSplitResponses = Table.ExpandListColumn(addSplitResponses, "response") in expandSplitResponsesThis creates a very long, but narrow, table - perfect for Power BI and reporting:
It may seem that your data getting to thousands/millions/billions of rows is a problem, but it's really not (unless you're using Direct Query, but probably out of scope here).
With this structure, you can create a simple COUNTROWS(tableName) measure, then use combinations of [name], [question], and [response] with this measure to create very fast and responsive visuals, even over millions/billions of rows.
Pete