Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

Unpivot question

Hi,

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

 

Germany
Fiscal Year16-Nov-1916-Dec-1916-Jan-2016-Feb-2016-Mar-2016-Apr-2016-May-2016-Jun-2016-Jul-2016-Aug-20
AM3122222222
EMEA2233332333
AP2322222221
AX2133333332
AD1422121113
AS2233434241
           
India
Fiscal Year16-Nov-1916-Dec-1916-Jan-2016-Feb-2016-Mar-2016-Apr-2016-May-2016-Jun-2016-Jul-2016-Aug-20
AM3122222222
EMEA2222222333
AP2222222221
AX2222223332
AD1422121113
AS2233434241
           
Italy
Fiscal Year16-Nov-1916-Dec-1916-Jan-2016-Feb-2016-Mar-2016-Apr-2016-May-2016-Jun-2016-Jul-2016-Aug-20
AM3122222222
EMEA3323332333
AP2222222221
AX3333333332
AD1211121113
AS2233434241
           
USA
Fiscal Year16-Nov-1916-Dec-1916-Jan-2016-Feb-2016-Mar-2016-Apr-2016-May-2016-Jun-2016-Jul-2016-Aug-20
AM3122222222
EMEA2222222333
AP2333232221
AX2122223332
AD1433331113
AS2212114241
           
UAE
Fiscal Year16-Nov-1916-Dec-1916-Jan-2016-Feb-2016-Mar-2016-Apr-2016-May-2016-Jun-2016-Jul-2016-Aug-20
AM3122222222
EMEA2222232222
AP2332323323
AX2222232222
AD1333323333
AS2121131211
1 ACCEPTED SOLUTION
v-cazheng-msft
Community Support
Community Support

Hi, @Anonymous 

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

 

1 Use Headers as First Row

v-cazheng-msft_0-1611106261120.jpeg

 

2 Transpose the table

v-cazheng-msft_1-1611106261125.jpeg

 

3 Use First Row as Headers

v-cazheng-msft_2-1611106261130.jpeg

 

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

v-cazheng-msft_3-1611106261132.png

 

v-cazheng-msft_4-1611106261134.png

 

v-cazheng-msft_5-1611106261137.jpeg

 

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

v-cazheng-msft_6-1611106261140.png

 

 

v-cazheng-msft_7-1611106261141.jpeg

The result looks like this:

v-cazheng-msft_8-1611106261147.jpeg

 

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.

 

View solution in original post

3 REPLIES 3
v-cazheng-msft
Community Support
Community Support

Hi, @Anonymous 

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

 

1 Use Headers as First Row

v-cazheng-msft_0-1611106261120.jpeg

 

2 Transpose the table

v-cazheng-msft_1-1611106261125.jpeg

 

3 Use First Row as Headers

v-cazheng-msft_2-1611106261130.jpeg

 

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

v-cazheng-msft_3-1611106261132.png

 

v-cazheng-msft_4-1611106261134.png

 

v-cazheng-msft_5-1611106261137.jpeg

 

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

v-cazheng-msft_6-1611106261140.png

 

 

v-cazheng-msft_7-1611106261141.jpeg

The result looks like this:

v-cazheng-msft_8-1611106261147.jpeg

 

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
Microsoft Employee
Microsoft Employee

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".

mahoneypat_0-1610909550575.png

 

Pat





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


Mikelytics
Resident Rockstar
Resident Rockstar

Hi @Anonymous ,

 

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

picture 1.PNG

Best regards,

Mikelytics

 

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

 

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

 

Appreciate your Kudos.

------------------------------------------------------------------
Visit my blog datenhungrig which I recently started with content about business intelligence and Power BI in German and English or follow me on LinkedIn!

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors