error in pivot column

[Anonymous]

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

can anyone advice where i have gone wrong...

Thank you so much.

[HotChilli]

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]

@HotChilli 
Here is the excel and the output below.

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.

[Ashish_Mathur · Accepted solution]

Hi,

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

[Anonymous]

hi all

@Ashish_Mathur @HotChilli 

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

[Ashish_Mathur]

Hi,

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

[HotChilli]

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

[HotChilli]

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]

@HotChilli 

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