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

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