Forum Discussion

rickettdev's avatar
rickettdev
New Member
6 years ago
Solved

Issue when transposing excel sheet with powerbi

Hey PBiers! I've got a selection of 40 different excel workbooks (all have the same structure in regards to columns and sheets etc) and i'm trying to merge them.

All Sheets are merging fine apart from 1 sheet which has columns going vertically, which is causing issues when attempting to transform it with PowerBI.

Within PowerBI Power Query Editor / transform it presents the data as 2 columns and 300 + rows. In fact it should be 18 columns and 19 rows.

When I use the transpose option I get 300 + columns 1.

However when I use the 'Transpose' function I end up with 1 row and 300 columns..

Any help advice on this would be awesome !

Need Help
  • v-lid-msft's avatar
    v-lid-msft
    6 years ago

    Hi rickettdev ,

     

    We can try to use the pivot feature in power query editor to meet your requirement:

     

     

    All the queries are here:

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WckxKVtJRMlSK1YGxjZRiYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", Int64.Type}}),
    #"Pivoted Column" = Table.Pivot(#"Changed Type", List.Distinct(#"Changed Type"[Column1]), "Column1", "Column2", List.Sum)
in
    #"Pivoted Column"

     


    Best regards,

     

4 Replies

  • kentyler's avatar
    kentyler
    Solution Sage
    6 years ago

    We'll probably need a sample of the misbehaving sheet to offer any meaningful advice.

    The first thought that occurs is to transform it in excel so that it looks like the other sheets, before loading it into power bi

     

    always take the easy way

    • rickettdev's avatar
      rickettdev
      New Member
      6 years ago

      kentyler thank you for getting back to me!

      Heres a example sheet (I have around 40 sheets I'm trying to merge)

      PropertiesValue
      Abc1someValue
      Abc2someValue
      Abc3someValue
      Abc4someValue
      Abc5someValue

       

      Example of two of the above merged. Once the sheets has been merged with PBI it looks like the below;

       

      PropertiesValue
      Abc1someValue
      Abc2someValue
      Abc3someValue
      Abc4someValue
      Abc5someValue
      Abc1someValue
      Abc2someValue
      Abc3someValue
      Abc4someValue
      Abc5someValue

       

      When I transpose this within PBI, I get a single row and multiple dubplicated colunms

      Abc1Abc2Abc3Abc4Abc5Abc1Abc2Abc3Abc4Abc5
      someValuesomeValuesomeValuesomeValuesomeValuesomeValuesomeValuesomeValuesomeValuesomeValue
      • v-lid-msft's avatar
        v-lid-msft
        Community Support
        6 years ago

        Hi rickettdev ,

         

        We can try to use the pivot feature in power query editor to meet your requirement:

         

         

        All the queries are here:

         

        let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WckxKVtJRMlSK1YGxjZRiYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", Int64.Type}}),
    #"Pivoted Column" = Table.Pivot(#"Changed Type", List.Distinct(#"Changed Type"[Column1]), "Column1", "Column2", List.Sum)
in
    #"Pivoted Column"

         


        Best regards,