cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
mellyVK Member
Member

error in pivot column

hi all..i am trying to pivot column"merged" but got error.

Capture.JPGCaptu22re.JPG

can anyone advice where i have gone wrong...

Thank you so much.

1 ACCEPTED SOLUTION

Accepted Solutions
Super User IV
Super User IV

Re: error in pivot column

Hi,

See if my solution here helps - Append data from multiple worksheets of multiple workbooks where each worksheet has a different head....


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

8 REPLIES 8
Super User I
Super User I

Re: error in pivot column

It's a bit difficult to debug because even though you have included the pbix, we don't have access to the source spreadsheet.

You could post the spreadsheet and tell us what your desired outcome is..

Having said that, the most common pivot error is "There were too many elements in the enumeration to complete the operation".

Is that what you have?

 

If so, it is usually because there's no aggregation on the pivot.  So powerbi doesn't know how to cope with rows that have a similar 'key'.

 

mellyVK Member
Member

Re: error in pivot column

@HotChilli 
Here is the excel and the output below.


Capture.PNG
As the file was also used among the team, so i was wondering if changes can be done within power bi itself or minimum change to excel.
Thank you for the advice.

Super User I
Super User I

Re: error in pivot column

I imported each sheet as a different query-> then transformed each sheet similarly ->

I appended the 3 queries together to get the final formatted data

 

Here's the advanced editor code for Sheet1. 

let
    Source = Excel.Workbook(File.Contents("J:\data\powerbiForum\zshop.xls"), null, true),
    Sheet2 = Source{[Name="Sheet1"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Sheet2,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}, {"Column10", type text}, {"Column11", type text}, {"Column12", type text}, {"Column13", type text}, {"Column14", type text}}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
    #"Filtered Rows" = Table.SelectRows(#"Promoted Headers", each ([City] = "New York" or [City] = "Ohio")),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Filtered Rows", {"City", "Product ID"}, "Attribute", "Value")
in
    #"Unpivoted Other Columns"

So if you create 3 queries and copy this code for each, you'll need to alter the line "Source{[Name="SheetX.......

replacing X with the Sheet number.

Once you've done that 'Append Queries as new' and tidy up the column names and data types.  Like this

et
    Source = Table.Combine({Sheet1, Sheet2, Sheet3}),
    #"Renamed Columns" = Table.RenameColumns(Source,{{"Attribute", "Date"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Value", Int64.Type}, {"Date", type date}})
in
    #"Changed Type"
mellyVK Member
Member

Re: error in pivot column

@HotChilli 

just curious, if new a data sheet is added to this excel, say 2020 data, will it get refreshed?

Super User IV
Super User IV

Re: error in pivot column

Hi,

See if my solution here helps - Append data from multiple worksheets of multiple workbooks where each worksheet has a different head....


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

mellyVK Member
Member

Re: error in pivot column

hi all

@Ashish_Mathur @HotChilli 

i was testing with another simple file as follow, but encounter same error also...any idea?

 

Capture.PNG

Super User I
Super User I

Re: error in pivot column

The second file is not in the same format as the one you posted earlier.

The second one has a Merge column which contains the data from 2 columns in the first file, so that's why it's not working

Super User IV
Super User IV

Re: error in pivot column

Hi,

My method should work.  Please retry.  I am travelling and therefore cannot try my method on your file right now.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
Coming Soon: T-Shirt Design Contest

Coming Soon: T-Shirt Design Contest

Keep your eyes open for our upcoming T-shirt design contest!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors