Using Microsoft Form Data

[Anonymous]

I was wondering if I could get a clever solution from you guys.  

I have data being collected from a Microsoft form.  I was wondering if you guys had a more elegant way of capturing the data as it comes through. 

Right now I use a 'Conditional Column' and look for the entire string. If it is there it sets a new column to true.   I have to create a new column for each answer with this method 😞 . This is becoming cumbersome.

My overall desire is to have the data be used easily in graphs and charts.

Below is an example dump of data.

Attribute	Value
Detail on Urgency Failure	Did Not Capture Answers to Urgency Questions in Description;
Detail on Urgency Failure	Did Not Document Information About the Work-Around;
Detail on Urgency Failure	Did Not Capture Answers to Urgency Questions in Description;
Detail on Urgency Failure	Did Not Capture Answers to Urgency Questions in Description;
Detail on Urgency Failure	Did Not Capture Answers to Urgency Questions in Description;
Detail on Urgency Failure	Did Not Capture Answers to Urgency Questions in Description;
Detail on Urgency Failure	Did Not Document in the Description Why the Urgency was Changed;Did Not Capture Answers to Urgency Questions in Description;
Detail on Urgency Failure	Urgency Does Not Match Description;
Detail on Urgency Failure	Did Not Capture Answers to Urgency Questions in Description;
Detail on Urgency Failure	Did Not Capture Answers to Urgency Questions in Description;
Detail on Urgency Failure	Did Not Capture Answers to Urgency Questions in Description;
Detail on Urgency Failure	Did Not Capture Answers to Urgency Questions in Description;
Detail on Urgency Failure	Did Not Capture Answers to Urgency Questions in Description;
Detail on Urgency Failure	Did Not Capture Answers to Urgency Questions in Description;
Detail on Urgency Failure	Did Not Capture Answers to Urgency Questions in Description;
Detail on Urgency Failure	Did Not Capture Answers to Urgency Questions in Description;
Detail on Urgency Failure	Did Not Capture Answers to Urgency Questions in Description;
Detail on Urgency Failure	Did Not Capture Answers to Urgency Questions in Description;
Detail on Urgency Failure	Did Not Capture Answers to Urgency Questions in Description;
Detail on Urgency Failure	Did Not Capture Answers to Urgency Questions in Description;
Detail on Urgency Failure	Did Not Capture Answers to Urgency Questions in Description;
Detail on Urgency Failure	Did Not Capture Answers to Urgency Questions in Description;
Detail on Urgency Failure	Did Not Capture Answers to Urgency Questions in Description;
Detail on Urgency Failure	Did Not Document Information About the Work-Around;Did Not Capture Answers to Urgency Questions in Description;
Detail on Urgency Failure	Urgency Does Not Match Description;
Detail on Urgency Failure	Did Not Document Information About the Work-Around;
Detail on Urgency Failure	Did Not Document in the Description Why the Urgency was Changed;Did Not Document Information About the Work-Around;Did Not Capture Answers to Urgency Questions in Description;
Detail on Urgency Failure	Did Not Capture Answers to Urgency Questions in Description;
Detail on Urgency Failure	Did Not Document Information About the Work-Around;Did Not Capture Answers to Urgency Questions in Description;
Detail on Urgency Failure	Did Not Capture Answers to Urgency Questions in Description;
Detail on Urgency Failure	Urgency Does Not Match Description;
Detail on Urgency Failure	Did Not Capture Answers to Urgency Questions in Description;
Detail on Urgency Failure	Did Not Capture Answers to Urgency Questions in Description;
Detail on Urgency Failure	Urgency Does Not Match Description;
Detail on Urgency Failure	Did Not Document Information About the Work-Around;
Detail on Urgency Failure	Did Not Document Information About the Work-Around;
Detail on Urgency Failure	Urgency Does Not Match Description;
Detail on Urgency Failure	Did Not Document in the Description Why the Urgency was Changed;Did Not Capture Answers to Urgency Questions in Description;
Detail on Urgency Failure	Did Not Capture Answers to Urgency Questions in Description;
Detail on Urgency Failure	Did Not Capture Answers to Urgency Questions in Description;
Detail on Urgency Failure	Did Not Document in the Description Why the Urgency was Changed;Did Not Capture Answers to Urgency Questions in Description;
Detail on Urgency Failure	Urgency Does Not Match Description;
Detail on Urgency Failure	Did Not Capture Answers to Urgency Questions in Description;
Detail on Urgency Failure	Did Not Capture Answers to Urgency Questions in Description;
Detail on Urgency Failure	Did Not Capture Answers to Urgency Questions in Description;
Detail on Urgency Failure	Did Not Document Information About the Work-Around;Did Not Capture Answers to Urgency Questions in Description;Urgency Does Not Match Description;
Detail on Urgency Failure	Urgency Does Not Match Description;
Detail on Urgency Failure	Did Not Capture Answers to Urgency Questions in Description;
Detail on Urgency Failure	Did Not Document Information About the Work-Around;Did Not Capture Answers to Urgency Questions in Description;Urgency Does Not Match Description;

[Anonymous]

Hi,

I find difficulties in reaching a Forms source I did not created.You seems OK with source : for me Forms https URL  is not aknoloedge as a web source apparently : how did you got your Forms source from PBI Desktop ?

thx in advance

[Anonymous]

Since you did not create the source it will be difficult to import. 

https://davidlozzi.com/2018/02/06/use-microsoft-forms-to-collect-data-right-into-your-excel-file/

I used this guide previously to create a form with an excel file.  I would then import that file into power bi.

[v-jiascu-msft]

Hi @Anonymous,

I'm afraid I'm not quite clear about your scenario. 

1. Why didn't you have to create a new column? How?

2. Just look at the visual, we can easily get it with the visual "Matrix".

3. I don't see any "sets a new column to true".

Best Regards,

Dale

[Anonymous]

@v-jiascu-msftSure!

Ok sorry if I was not clear....

So let me start with what Power BI does automatically.  I will use the data I have posted in the earlier reply. 

If you just add that data to a matrix you will get this...

The problem is that the answers that feature 2 or more answers in the cell is treated as a unique reply.

This is what the data entry side looks like for the form....

-------------------------------------

What I was doing to seperate out the answer was to use a conditional column.

The statement looks for just part of the string and will make the new column get a value of 1 (true) if it is present in that cell.

I used "Did not capture answers to urgency question in description" for this example. 

I can then sum or use the columns to make an accuracte representation of when those options are selected.  Everything between a ; in the demo data are seperate selections.

I hope this cleared things up!  Let me know if it is still confusing. 

I really wish there was a way to let Power BI know the data in seperated by a ;

[v-jiascu-msft · Accepted solution]

Thank you, @Anonymous. It's quite clear now.

We can split the [Value] in the Query Editor. Then we can create a Matrix easily. Please check out the demo in the attachment.

Best Regards,

Dale

[Anonymous]

Was able to use that method in both excel and power bi,  thank you!