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