[DAX] how to change row context to column with text values #2

[PascalT]

I tried to edit my previous topic but i don's see my new post... so I'm creating a new one. Sorry Admis if i do it wrong 😞

Hi, well, i'm facing another challenge in the same order...

I want to reproduce the same kind of data manipulation but when i'm doing it, i've an error:

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

I've another XML in which i've several fields.

Here is an example of a record of the XML. Note that i can have up to 20 fields.

<r>
   <p>
      <name>Name</name>
      <value>John doe</value>
   </p>
   <p>
      <name>Company</name>
      <value>my company</value>
   </p>
   <p>
      <name>Email</name>
      <value>my@email.com</value>
   </p>
   <p>
      <name>Phone</name>
      <value>phone</value>
   </p>
   <p>
      <name>Comments</name>
      <value>comment</value>
   </p>
   <p>
      <name>title</name>
      <value />
   </p>
   <p>
      <name>coursenumber</name>
      <value>61600006-01</value>
   </p>
</r>

In PBI, i'm just interested by few fields, like name, country, email.. something like 5-10 fields maximum.

So this is how my data looks like.

 and the query is: = Table.SelectRows(#"Expanded p", each [p.name] = "Company" or [p.name] = "Country" or [p.name] = "Email" or [p.name] = "First name" or [p.name] = "Last name" or [p.name] = "sitecore coursenumber")

So I want to do the same pivot mecanism.

1> I select the first column "p.name"

2> Pivot column

> And then i got this:

> And the error is:

Do I do something wrong ? 

Thank you very Much

Pascal

[v-ljerr-msft]

Hi @PascalT,

As you have set the aggregation to Do Not Aggregate, if there were two or more rows that were pivoted had the same value, it will result in dupe column names. That was what was throwing error.

So if it is normal to have a name repeated in your source data, then an aggregation needs to be set properly.

Reference:

https://social.technet.microsoft.com/Forums/en-US/7923e025-a0c4-407b-bd0f-65959e5038bb/what-does-thi...

http://radacad.com/pivot-and-unpivot-with-power-bi

Regards

[PascalT]

Hi and thank you for your comment.

I'm back on this topic as i didnt solve my issue.

How do you do not aggregate properly?

Thank you very much for your help.

P