Issue when transposing excel sheet with powerbi

[rickettdev]

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 !

[kentyler]

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]

@kentyler thank you for getting back to me!

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

Properties	Value
Abc1	someValue
Abc2	someValue
Abc3	someValue
Abc4	someValue
Abc5	someValue

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

Properties	Value
Abc1	someValue
Abc2	someValue
Abc3	someValue
Abc4	someValue
Abc5	someValue
Abc1	someValue
Abc2	someValue
Abc3	someValue
Abc4	someValue
Abc5	someValue

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

Abc1	Abc2	Abc3	Abc4	Abc5	Abc1	Abc2	Abc3	Abc4	Abc5
someValue	someValue	someValue	someValue	someValue	someValue	someValue	someValue	someValue	someValue

[v-lid-msft]

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,

[v-lid-msft · Accepted solution]

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,