Forum Discussion

saravanan's avatar
saravanan
New Member
11 years ago
Solved

Issue with unpivoting Excel Data

Hello There

I am trying to unpivot the excel data but it was not correctly unpivoted 

see the below sample which i am trying to unvpoit from PBI designer . 

I have employee sales by name but Pivoted date values

EmpName12/12/201412/1/200412/14/201512/15/200212/16/198812/17/2014
Saravanan122023
Jeff1210891 5

i selected the file from PBI designer then i tried to edit the query to use the first row as headers then wanted to Unpivot the sales data by employee by date

PBI1.PNG

After i Clicked the Use First Row As Headers it ended up as below. it shows up ONLY the EmpName Column as Header but none of the dates become Header

First Row Click.PNG

and when i Unpivot the All Columns except EmpName it comes out like below which is totally wrong

UnPivot.PNG

 

it does works well if its CSV and i can see what i want as below

Correct Unpivot.PNG

i think this is because of Excel mixed data type but can this be fixed or is there any work around ?

 

thanks

Saravanan

  • Greg_Deckler's avatar
    Greg_Deckler
    11 years ago

    I believe the issue is that when you import from your Excel file, it is changing the types of the columns to "Any", should be the third step in your query. Go to View | Advanced Editor and edit the "any" to "text". Then and only then promote your headers.

     

    let
        Source = Excel.Workbook(File.Contents("C:\Temp\employees.xlsx"), null, true),
        Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
        #"Changed Type" = Table.TransformColumnTypes(Sheet1_Sheet,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}}),
        #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type")
    in
        #"Promoted Headers"

2 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    11 years ago

    I believe the issue is that when you import from your Excel file, it is changing the types of the columns to "Any", should be the third step in your query. Go to View | Advanced Editor and edit the "any" to "text". Then and only then promote your headers.

     

    let
        Source = Excel.Workbook(File.Contents("C:\Temp\employees.xlsx"), null, true),
        Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
        #"Changed Type" = Table.TransformColumnTypes(Sheet1_Sheet,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}}),
        #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type")
    in
        #"Promoted Headers"

    • saravanan's avatar
      saravanan
      New Member
      11 years ago

      smoupre,

       

      Great.. this was a quick FIX :)

      it worked

       

      thanks