Convert jotfom data to table

[dtuan81]

Hello everyone,

I'm a new comer here. Currently, I'm working with Jotform app to digitalize form for my company. It is simple and good platform to digitalize form. But I also want to use the collected data for analysis.

The below screenshot is the form I created to collect data:

When I downloaded the submitted data, Jotfom provided the Excel file which it has converted all data in table to column as  shown in the attached file, sheet named "raw data".

I want to restructure these data to a meaningful table where I can buid a PowerBI dashboard. My expecting data structure is shown in the sheet named "Expecting data".

Could anyone assist me or point me to resources for this? I've tried searching online but haven't been successful. I would greatly appreciate any help you can offer. Thank you!

PS: This is a link of my file

[v-pagayam-msft]

Hi @dtuan81 ,

Thank you for reaching out, and welcome to the Fabric Community!

Please find below step-by-step instructions to reshape your data using Power Query:

1.Load your Jotform export table into Power Query.

2.In Power Query, select all columns that are not activity-related, such as Submission Date, Name, Email, Date, Comments.

Right-click these columns and click “Unpivot Other Columns”.This will convert your data into two columns:
Attribute stores the original column names like 1. Cutting/Packing >> 1 >> Size. Value stores the data entered in the form fields

3.Select the Attribute column, go to Transform ,Split Column by Delimiter ,use '>>' as the delimiter

Split into columns. Rename the resulting columns to Activity, RowNumber, FieldName

4.Select Submission Date, Name, Email, Date, Activity, RowNumber. Go to Transform and select Pivot Column.Set FieldName as the column to pivot

Use Value as the values column

Once done, you may get a clean, normalized table where each row represents one form entry line, making it easy to analyze by activity, date, or user.

Please refer the below screenshot and file for your reference.

If this answer meets your requirements,give us kudos and consider accepting t as solution.If you still face any issues, feel free to reachout!

Thank you.

Regards,
Pallavi.

[dtuan81]

Hi @v-pagayam-msft ,

Thanks for your support!

But I want my database presented as shown in the below screenshot: the information regarding Size, Meter, Pcs, Hours, Job#, and Notes is being repeated. I would prefer these details to be organized in columns corresponding to each activity, such as 1. Cutting/Packing, 2. Threading, etc.

Could you please review it again and assist me with this? Thank you!