Transforming a field capture from a Form into an Excel Spreadsheet

[MikeLem]

Hi There .. my first post to the Forum, as a new user of Power BI

I'm using a Form to capture survey questions, then extracting the results into an Excel Spreadsheet, and now want to Load the data into Power BI.

There is one question in the Survey that allows the person responding to select multiple relevant selections .. giving me the following in Excel: ["Java","C#","Rust","Html"]

My challenge is how do I split this field into multiple individual values, columns or rows?

Any suggestions would be greatly appreciated.

Thanks...

[MikeLem]

Hey Greg .. would I be able to create a similar format that is automatically created by the Form .. my goal is to create a dashboard with some of the similar displays as in the Form .. but .. replace others with something better .. I'm working through your suggested approach ...  the replace and split .. worked perfectly .. struggling with the .. Unpivot of the 3 columns returing something I can then use to replicate the following.. 

[Greg_Deckler]

@MikeLem First, thanks so much! Is there a chance you could post some sample data as text? I could try to mock some things up for you that way and share a PBIX file. What I have in my head is that if you unpivot the columns then you could use something like COUNTROWS(DISTINCT('Table'[Person])) as a measure to provide your value but would need to better understand the data to be certain something like that would work.

[MikeLem]

@Greg_Deckler 

Hey Greg .. thanks for the responses, much appreciated.

Below is what is extracted from the Form responses .. the multiple selection survey questions are the ones that are now the challenge to display properly.

Any insights you could provide would be greatly appreciated .. I'm just starting your book .. so .. if you could point me to sections in your book that I should jump to, to solve this challenge .. that also would be ... great .. 

Column Headings

ID

Start timeWhat programming languages do you usually use?

In which of the following ways did you use Copilot: (Multiple Selection, Select all that apply)

I am more productive when using Copilot

I spend less time searching for information or examples when using Copilot
Using Copilot helps me stay in the flow
I learn from the suggestions Copilot shows me
How likely are you to recommend that your team continues to use Copilot?
Is there anything else you would like to add about your experience with Copilot?

16	10/02/2023 12:08	["TypeScript"]	["To explore a new language, framework, or API","For everyday coding work in a familiar language"]	6	9	5	9	[" Very likely"]
17	10/02/2023 13:15	["TypeScript","JavaScript"]	["For everyday coding work in a familiar language"," For everyday coding work in a unfamiliar language"," To write tests in a familiar language","To write tests in a unfamiliar language","To explore a new language, framework, or API"]	7	7	5	8	[" Very likely"]
18	10/02/2023 18:49	["JavaScript","C#"]	[" For everyday coding work in a unfamiliar language","To explore a new language, framework, or API"]	10	10	10	10	["Extremely likely"]
20	10/02/2023 19:03	["TypeScript","JavaScript"]	["To write particularly repetitive code (boilerplate) in an unfamiliar language","To explore a new language, framework, or API"]	6	10	9	9	[" Somewhat likely"]
21	10/03/2023 4:13	["TypeScript","Go","JavaScript"]	["For everyday coding work in a familiar language"," To write tests in a familiar language","To explore a new language, framework, or API"]	6	8	7	8	[" Very likely"]
22	10/03/2023 17:21	["JavaScript","TypeScript"]	["To write particularly repetitive code (boilerplate) in a familiar language","For everyday coding work in a familiar language"," To write tests in a familiar language"]	7	9	9	4	["Extremely likely"]
23	10/03/2023 17:37	["TypeScript","JavaScript","Html","CSS, SASS"]	["For everyday coding work in a familiar language"," For everyday coding work in a unfamiliar language"]	6	10	8	8	["Extremely likely"]	I use co-pilot chat way more than the auto-complete at the moment.

[Greg_Deckler]

@MikeLem Take a look at the attached PBIX below signature. I did transformations using the JSON transformation and then expanded the List to rows. You can then use a distinct count of ID to get counts of things. Created a few different visuals to show this.

[MikeLem]

@Greg_Deckler 

Hey Greg .. wow ..thanks for doing all of this, much appreciated.

When I open the .pbix file in Power BI Desktop .. nothing seems to .. display .. likely a real newby issue.. argh..

[Greg_Deckler · Accepted solution]

@MikeLem Maybe I forgot to save. Attaching again after I made sure to save.

[MikeLem]

@Greg_Deckler ah.. that worked perfectly .. looks like I have a lot to learn with the ... Transform, Clean Up of the data, 

Greg .. thanks for all of your help .. much appreciated .. now back to going through your book .. 

[Greg_Deckler]

@MikeLem In Power Query Editor you can replace ", [ and ] with nothing and then split the column using , as the separator. You could then unpivot the column.

[MikeLem]

@Greg_Deckler 

Hey Greg .. would I be able to create a similar format that is automatically created by the Form .. my goal is to create a dashboard with some of the similar displays as in the Form .. but .. replace others with something better .. I'm working through your suggested approach ...  the replace and split .. worked perfectly .. struggling with the .. Unpivot of the 3 columns returing something I can then use to replicate the following.. 

[MikeLem]

Hey Greg .. thanks for the prompt response, much appreciated .. so much .. I purchased your book both in Kindle and Paper Back versions ... I will give this a try ..

Thanks again, have a great day.

Mike Lemmon