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

[dtuan81]

Hi @v-pagayam-msft ,

Great, you make my day! Thank you!

[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!

[v-pagayam-msft]

Hi @dtuan81 ,
Thank you again for the update. I am happy to assist you!

Please find the attached pbix file and screenshot for your reference.

I hope this helps. If this answer meets your requirements, consider accepting it as solution.

Thank you.

[dtuan81]

Hi @v-pagayam-msft ,

Thanks for your response!

You pbix file is exactly what I want. But could you please show me how you convert my database to your layout? Thanks!

[v-pagayam-msft · Accepted solution]

Hi @dtuan81 ,
Thank you for the follow-up!

Please go through the following steps :

1.Load the Excel file.

2.Select all columns excluding: Submission Date, Name, Email, and Date. Right-click on the selected columns and choose Unpivot Columns.
3.Split the Attribute column using the >> delimiter, then trim any whitespace. This will separate the values into three parts.Name them as : Task Category (for 1. Cutting/Packing), Row Number (for 1), and Column Header (for Size, Meter). These fields help normalize the data for easy pivoting and analysis in Power BI.
4.To structure your data for Power BI, pivot the 'Column Header' column in Power Query. Keep Submission Date, Name, Email, Date, Task Category, and Row as identifiers. Pivot Column Header so fields like Size, Meter, Pcs, etc., become columns, using Value as the data source. This converts your long-format rows into a clean, tabular format for analysis.

For better understanding, go to tranform and see each step.

Thank you.

Glad it helped. give us kudos and consider accepting it as solution.

Thank you.