CODEWITHSUNDEEP
pythonexcelpowerquerypowerbi-desktopm
udara Nadeeshani
udara Nadeeshani

Reputation: 41

Is there any way to rearrange excel data without copy paste?

I have an excel file that contain country name and dates as column name.

+---------+------------+------------+------------+
| country | 20/01/2020 | 21/01/2020 | 22/01/2020 |
+--------- ------------+------------+------------+
| us      |   0        |   5        |       6    |
+---------+------------+------------+------------+
| Italy   | 20         |   23       |      33    |
+--------- ------------+------------+------------+
| India   |   0        |   0        |       6    |
+---------+------------+------------+------------+

But i need to arrange column names country, date, and count. Is there any way to rearrange excel data without copy paste.

final excel sheet need to look like this

+---------+------------+------------+
| country | date       | count      | 
+--------- ------------+------------+
| us      | 20/01/2020 |   0        | 
+---------+------------+------------+
| us      | 21/01/2020 |   5        |   
+---------+------------+------------+
| us      | 22/01/2020 |   6        |    
+---------+------------+------------+
| Italy   | 20/01/2020 |   20       |     
+--------- ------------+------------+
| Italy   | 21/01/2020 |   23       |     
+--------- ------------+------------+
| Italy   | 22/01/2020 |   33       |      
+--------- ------------+------------+
| India   | 20/01/2020 |   0        | 
+---------+------------+------------+

Upvotes: 0

Views: 108

Answers (2)

Gary's Student
Gary's Student

Reputation: 96763

Power Pivot is the best way, but if you want to use formulas: In F1 enter:

=INDEX($A$2:$A$4,ROUNDUP(ROWS($1:1)/3,0))

and copy downward. In G1 enter:

=INDEX($B$1:$D$1,MOD(ROWS($1:1)-1,3)+1)

and copy downward. H1 enter:

=INDEX($B$2:$D$4,ROUNDUP(ROWS($1:1)/3,0),MOD(ROWS($1:1)-1,3)+1)

and copy downward

enter image description here

The 3 in these formulas is because we have 3 dates in the original table.

Upvotes: 1

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60289

Unpivot using Power Query:

  • Data --> Get & Transform --> From Table/Range
  • Select the country column
    • Unpivot Other columns
  • Rename the resulting Attribute and Value columns to date and count
  • Because the Dates which are in the header are turned into Text, you may need to change the date column type to date, or, as I did, to date using locale

enter image description here

M-Code

    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"country", type text}, {"20/01/2020", Int64.Type}, {"21/01/2020", Int64.Type}, {"22/01/2020", Int64.Type}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"country"}, "date", "count"),
    #"Changed Type with Locale" = Table.TransformColumnTypes(#"Unpivoted Other Columns", {{"date", type date}}, "en-150")
in
    #"Changed Type with Locale"

Upvotes: 4

Related Questions