cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
iDusk258
New Member

modeling data

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.

iDusk258_0-1638553512759.png

 

 

 

1 ACCEPTED SOLUTION
sreenathv
Super User
Super User

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)

sreenathv_0-1638600759243.png

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.

sreenathv_1-1638600928288.png

It will give you the following...

sreenathv_2-1638601051513.png

Step 2: Transpose this table using the following option.

sreenathv_3-1638601106895.png

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

sreenathv_4-1638601152410.png

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

sreenathv_5-1638601236167.png

This will give you the following output.

sreenathv_6-1638601289710.png

Step 4: Promote the first row as headers

sreenathv_7-1638601361209.png

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

sreenathv_8-1638601467159.png

This will give you the following output.

sreenathv_9-1638601506965.png

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

sreenathv_10-1638601622398.png

This will give you the following output.

sreenathv_11-1638601652148.png

 

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.

 

 

 

 

 

 

 

 

 

 

 

View solution in original post

1 REPLY 1
sreenathv
Super User
Super User

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)

sreenathv_0-1638600759243.png

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.

sreenathv_1-1638600928288.png

It will give you the following...

sreenathv_2-1638601051513.png

Step 2: Transpose this table using the following option.

sreenathv_3-1638601106895.png

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

sreenathv_4-1638601152410.png

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

sreenathv_5-1638601236167.png

This will give you the following output.

sreenathv_6-1638601289710.png

Step 4: Promote the first row as headers

sreenathv_7-1638601361209.png

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

sreenathv_8-1638601467159.png

This will give you the following output.

sreenathv_9-1638601506965.png

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

sreenathv_10-1638601622398.png

This will give you the following output.

sreenathv_11-1638601652148.png

 

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.

 

 

 

 

 

 

 

 

 

 

 

Helpful resources

Announcements
Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

May 23 2022 episode 5 with aka link.jpg

The Power BI Community Show

Welcome to the Power BI Community Show! Jeroen ter Heerdt will talk about the importance of Data Modeling.

May UG Leader Call Carousel 768x460.png

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!