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 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.