Pivoting problem: "There were too many elements in the enumeration to complete the operation"

[Paulien1989]

Hi! And help!

In a Query I unpivoted several columns, transformed them, and then tried to pivot two other columns. I end up with 68 errors (of 673 rows) saying: "There were too many elements in the enumeration to complete the operation" 

I kind of get why but I'm looking for the solution/work around.

What I do is the following:
- I have loaded a table from Google Analytics containing date, page and pageviews. Page contains the URLs for filtercriteria selected during a search. For instance, a URL can be /facets/pancakes,chocolate,quickcook,healthy
- First I extracted the URL after /facet/, leaving only the search criteria (checkboxes) sepertated by comma's
- Then I split the columns on the comma, ending up with columns Criteria.1, Criteria.2, Criteria.3 and Criteria.4
- Then I unpivoted these columns so that all criteria end up in one single column called "Criteria"
- Then I created a calculated column named "Category" and did rules like "When Criteria contains pancakes then Food", "When Criteria contains chocolate then Food", "When Criteria contains quickcook then "Tempo Cooking", else "Healthiness category"

- Then I tried to re-pivot the columns Criteria and Category again to create one column per category (so 3 of them) and their values either being null or filled in. When I do this, 68 rows return errors:

This basically was my solution to structurize the used search criteria on the row of the URL+date (instead of the criteria being in one string with no absolute order). But of course, it's not copying rows when there's more than 1 value to a category. Which I do want it to do, so ideally the columns would look like this:

Anyone know how?

[v-juanli-msft]

Hi 

After testing, I can't change the dataset to the last one, I would recommend you to structure the dataset before importing to Power BI.

Best Regards

Maggie

[Paulien1989]

What do you mean by that?

I can't do much about the data before importing, because it's from google analytics, and basically what you see is what you get.