Unpivot question

[Anonymous]

Hi,

I've data set like this for many countries. How to unpivot and transpose this type of excel table? 

Germany
Fiscal Year	16-Nov-19	16-Dec-19	16-Jan-20	16-Feb-20	16-Mar-20	16-Apr-20	16-May-20	16-Jun-20	16-Jul-20	16-Aug-20
AM	3	1	2	2	2	2	2	2	2	2
EMEA	2	2	3	3	3	3	2	3	3	3
AP	2	3	2	2	2	2	2	2	2	1
AX	2	1	3	3	3	3	3	3	3	2
AD	1	4	2	2	1	2	1	1	1	3
AS	2	2	3	3	4	3	4	2	4	1
India
Fiscal Year	16-Nov-19	16-Dec-19	16-Jan-20	16-Feb-20	16-Mar-20	16-Apr-20	16-May-20	16-Jun-20	16-Jul-20	16-Aug-20
AM	3	1	2	2	2	2	2	2	2	2
EMEA	2	2	2	2	2	2	2	3	3	3
AP	2	2	2	2	2	2	2	2	2	1
AX	2	2	2	2	2	2	3	3	3	2
AD	1	4	2	2	1	2	1	1	1	3
AS	2	2	3	3	4	3	4	2	4	1
Italy
Fiscal Year	16-Nov-19	16-Dec-19	16-Jan-20	16-Feb-20	16-Mar-20	16-Apr-20	16-May-20	16-Jun-20	16-Jul-20	16-Aug-20
AM	3	1	2	2	2	2	2	2	2	2
EMEA	3	3	2	3	3	3	2	3	3	3
AP	2	2	2	2	2	2	2	2	2	1
AX	3	3	3	3	3	3	3	3	3	2
AD	1	2	1	1	1	2	1	1	1	3
AS	2	2	3	3	4	3	4	2	4	1
USA
Fiscal Year	16-Nov-19	16-Dec-19	16-Jan-20	16-Feb-20	16-Mar-20	16-Apr-20	16-May-20	16-Jun-20	16-Jul-20	16-Aug-20
AM	3	1	2	2	2	2	2	2	2	2
EMEA	2	2	2	2	2	2	2	3	3	3
AP	2	3	3	3	2	3	2	2	2	1
AX	2	1	2	2	2	2	3	3	3	2
AD	1	4	3	3	3	3	1	1	1	3
AS	2	2	1	2	1	1	4	2	4	1
UAE
Fiscal Year	16-Nov-19	16-Dec-19	16-Jan-20	16-Feb-20	16-Mar-20	16-Apr-20	16-May-20	16-Jun-20	16-Jul-20	16-Aug-20
AM	3	1	2	2	2	2	2	2	2	2
EMEA	2	2	2	2	2	3	2	2	2	2
AP	2	3	3	2	3	2	3	3	2	3
AX	2	2	2	2	2	3	2	2	2	2
AD	1	3	3	3	3	2	3	3	3	3
AS	2	1	2	1	1	3	1	2	1	1

[v-cazheng-msft · Accepted solution]

Hi, @Anonymous 

You can use the following steps to unpivot and transpose the data. Here is the sample.

1 Use Headers as First Row

2 Transpose the table

3 Use First Row as Headers

4 Add a Custom column with country name and drag it to proper location to reorder the column

5 Do the same operations to other queries and then Append Queries as New to get a new query

The result looks like this:

Best Regards,

Caiyun Zheng

Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[mahoneypat]

Here is one way to do it, with the Shift and Fill Down approach.  To see how it works, just create a blank query, go to Advanced Editor, and replace the text there with the M code below.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("5ZZdC4IwFIb/SnitkB8EXQ7SSDACCQrxYpmEYBaWgf8+pzbnnLMvKBQ8nNeXw3Hw7Mw5jjD34yOMUkEUeI8rOoIRXDwYjrY+jDNLnkjL002Sp4We+R7WJowkZVxow99hbcEYa3COCT/F2kwiQodVfXJAGq0DWJmlIjsLhRuoXLd0QJgqFXUv778ibF5/uSjf4Ndm9/qX8vJZWarVOlVZxq3ycpuxdo3ISpmLxYwy9Vyg6kW0D+BgyLOihXxXUOT5nf+S/BWG/Z959ox/izxv1lvI06R/QH5tg95zf2PiWduj46x/ceLprdHBnd4eH3MH+iC5N3/iTO4KlSvNPelbuj+48y8bDe40c7Xhue4d", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t, Column6 = _t, Column7 = _t, Column8 = _t, Column9 = _t, Column10 = _t, Column11 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}, {"Column10", type text}, {"Column11", type text}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Column1] <> " ")),
    #"Added Custom" = Table.AddColumn(#"Filtered Rows", "Country", each if [Column2] = "" then [Column1] else null),
    #"Filled Down" = Table.FillDown(#"Added Custom",{"Country"}),
    #"Filtered Rows1" = Table.SelectRows(#"Filled Down", each ([Column2] <> "")),
    #"Promoted Headers" = Table.PromoteHeaders(#"Filtered Rows1", [PromoteAllScalars=true]),
    #"Filtered Rows2" = Table.SelectRows(#"Promoted Headers", each ([Fiscal Year] <> "Fiscal Year")),
    #"Renamed Columns" = Table.RenameColumns(#"Filtered Rows2",{{List.Last(Table.ColumnNames(#"Filtered Rows2")), "Country"}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Renamed Columns", {"Fiscal Year", "Country"}, "Attribute", "Value"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Unpivoted Other Columns",{{"Country", type text}, {"Attribute", type date}}),
    #"Renamed Columns1" = Table.RenameColumns(#"Changed Type1",{{"Fiscal Year", "Region"}})
in
    #"Renamed Columns1"

Here is what the output looks like using your example data (I wasn't sure what to call your first column so I went with "Region".

Pat

[Mikelytics]

Hi @Anonymous ,

In the POwer Query Editor please mark the column with the areas and click the following:

 

Best regards,

Mikelytics

-------------------------------------------------------------------------------

Did I answer your request? Please mark my post as solution.

Appreciate your Kudos.