Dataflow refresh problem when using pivot table

[leews]

Hi,

When adding a pivot table step in dataflow, we receive the message that "There was a problem refreshing your dataflow". At the error details, show the following message:

"Expression.Error: There were too many elements in the enumeration to complete the operation."

It works fine in Power Query Online and when copying to the Power BI Desktop, it works fine too.

Follow the M code of the query:

let
  Source = Excel.Workbook(Web.Contents("-"), null, true),
  Custom = Table.AddColumn(Source, "Custom", each Table.PromoteHeaders([Data])),
  #"Custom 1" = Table.Combine(Custom [Custom]),
  #"Changed column type" = Table.TransformColumnTypes(#"Custom 1", {{"1", type text}, {"2", type text}, {"3", type text}, {"4, type text}, {"5", type text}}),
  #"Pivoted Column" = Table.Pivot(#"Changed column type", List.Distinct(#"Changed column type"[#"Attribute Description"]), "4", "5"),
  #"Removed Duplicates" = Table.Distinct(#"Pivoted Column", {"1"}),
  #"Removed blank rows" = Table.SelectRows(#"Removed Duplicates", each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null}))),
  #"Filtered Rows" = Table.SelectRows(#"Removed blank rows", each [1] <> "" and [1] <> null)
in
  #"Filtered Rows"

There is no date columns and when removing the Table.Pivot step, it works fine.

Can you help us please?

Thank you very much

[az38 · Accepted solution]

Hi @leews 

all the roads lead to duplicate column names or according @ImkeF post here https://community.powerbi.com/t5/Power-Query/There-were-too-many-elements-in-the-enumeration-to-comp... 

if the remaining columns (those who are not unpivoted and are not in the values-section) don't provide a unique key to the result of the pivot.

check it 

The difference in PQ online, desktop and service behaviour is because desktop uses only preview of data