I have 4 columns before I pivot the table: ID, Company, Field, and Data.
ID is all unique ID's. Company is all 'LLC', Field is composed of 'Date', 'Salary', and 'Department', and Data is comprised of all the data for Date, Salary and Department. When I use the pivot action to create a new table of columns: ID, Company, Date, Salary, and Department, I lose a lot of data. Since the Company column is the same for each ID, the new table after Pivot only keeps 1 row per ID. I am supposed to have multiple rows depending on the Department. For example, if there are 3 unique Departments in the Data column, I should have 3 rows per ID.
When you load your data, the system is not supposed to know that the row with a department value is linked to the 2 following rows and then the enumeration start again with the new department value.
the trick with Pivot action is to insert an action before regarding what you want to obtain, try with enumerate rows (with enumerates in group), enumerate group based on the combination of fields or running total transformation with count option and see the impact on the results.
remove the inserted sequence after the pivot transformation.
As there is no example attached, I tried to reproduce the issue based on your description. I assume you're using the "Any" aggregation function that picks one arbitrary value from each subset of values related to each combination of group columns (columns that are not a label or data), in your case it's ID and Company. But since ID and Company are the same for all rows, they comprise only one aggregation group from which the "Any" aggregation function picks only one value and discards the others.
To resolve the problem, make Department a group column, instead of values in the aggregated column. See the example below:
