Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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; |
Solved! Go to 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
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
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.
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
@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 ;
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
Was able to use that method in both excel and power bi, thank you!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
97 | |
78 | |
63 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |