March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
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 |
Solved! Go to 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.
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.
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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
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.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
User | Count |
---|---|
93 | |
91 | |
90 | |
80 | |
49 |
User | Count |
---|---|
160 | |
145 | |
103 | |
72 | |
55 |