modeling data

[iDusk258]

I'm having a great deal of difficulty in doing the data modeling, could someone give me a light on how to make this table
with the perfect columns transpose the dates contained in it?

 just wanted to be able to separate each code by dates so I could create views

I've never taken data structured this way, so I don't know how to proceed.

A preview of this data is attached.

[sreenathv · Accepted solution]

You could transform this into a proper table. I will post an example below...

Let's say, you are starting with the following imported data... (remove any step that promotes 1st row as headers)

Step 1: Identify the pivot columns. In this case, those are the first 3 columns. Select those 3 pivot columns and merge them together, use a delimiter like a semicolon (;) or any other character that won't be there in the data values of these columns.

It will give you the following...

Step 2: Transpose this table using the following option.

This will transpose the table and give you the following...

Step 3: Use "Fill Down" on those pivot columns that have null values in them.

This will give you the following output.

Step 4: Promote the first row as headers

Step 5: Select the first 3 Pivot columns and unpivot the other columns. (Use Shift+Click or Ctrl+Click to select multiple columns)

This will give you the following output.

Step 6: Split back the merged column using the same delimiter that was used earlier.

This will give you the following output.

You could further rename the column names, change the data types, and so on. Use these steps as a general idea. The actual steps that you need could be different depending on your input file. But it will be a combination of similar steps. 

You don't have to know any advanced stuff to do all these. These are pretty simple steps like merging, transposing, splitting, unpivoting, renaming column headers, choosing the correct data types etc... If you observe your input file and think about how to sequence these simple transformations to get the final output, you will be able to do it.