Forum Discussion
Convert jotfom data to table
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.
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.
Hi Anonymous ,
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!
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.Hi Anonymous ,
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!
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.