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 fr...
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,

     

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,

     

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

    Hi rickettdev ,

     

    How about the result after you follow the suggestions mentioned in my original post?Could you please provide more details about it If it doesn't meet your requirement?


    Best regards,