Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

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
HotChilli
Super User
Super User

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'.

 

Anonymous
Not applicable

@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.

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/
Anonymous
Not applicable

hi all

@Ashish_Mathur @HotChilli 

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

 

Capture.PNG

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/

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

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"
Anonymous
Not applicable

@HotChilli 

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.