cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mellyVK Post Patron
Post Patron

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 Post Patron
Post Patron

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 Post Patron
Post Patron

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

Highlighted
mellyVK Post Patron
Post Patron

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
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors