Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi DAX experts,
<Edit 10/03/2017>
<==========>
I've added another related issue at the bottom. Thanks to all for your help!
<==========>
I have the following data coming form an XML. The table looks like this. I only have 4 Keys: [Organisation], [Country], [Event-date], [Phonenumber].
EmailAddress | Key | Value |
rose@apple.it | [Organisation] | Apple |
rose@apple.it | [Country] | Italy |
rose@apple.it | [Event-date] | 23/12/2016 |
rose@apple.it | [PhoneNumber] | +39 2323 2323 |
pascal@ibm.dk | [Organisation] | IBM |
pascal@ibm.dk | [Country] | Denmark |
julie@microsoft.fr | [PhoneNumber] | +33 1234 5645 |
julie@microsoft.fr | [Country] | France |
Each EmailAddress could have one, two, three or the four Key values filled (for exemple, in this table, julie@microsoft.com has only the [Phonenumber] and the [Country] keys filled).
My objective is to get this result.
EmailAddress | [Organisation] | [Country] | [Event-date] | [PhoneNumber] |
rose@apple.it | Apple | Italy | 23/12/2016 | +39 2323 2323 |
pascal@ibm.dk | IBM | Denmark | ||
julie@microsoft.fr | France | +33 1234 5645 |
Of course, i can have thousands of different EmailAddress, [Organisation] and [PhoneNumber].
I've tried to apply the principle from this http://community.powerbi.com/t5/Desktop/DAX-how-to-change-row-context-to-column/m-p/42616#M16312 but it doesnt work as I can't do any SUM, MIN, MAX etc... with text values.
Could an expert help me ?
Thank you very much.
Pascal
Solved! Go to Solution.
In the Query Editor, select the Key column and hit the "Pivot Column" button in the Transform tab. Open up the Advanced Options and select "Do Not Aggregate" and set Values as the Values column.
Edit: now with helpful screenshots!
Proud to be a Super User!
When you load the results into the actual Power BI data model the null values should all load as blank. Are you literally getting the word "null" when you load?
You can force a blank by hitting the Replace Values button. Type null in the Value To Find box and just leave the Replace With box empty. But you shouldn't have to if they're truly null values and not the actual text "null".
Proud to be a Super User!
In the Query Editor, select the Key column and hit the "Pivot Column" button in the Transform tab. Open up the Advanced Options and select "Do Not Aggregate" and set Values as the Values column.
Edit: now with helpful screenshots!
Proud to be a Super User!
Wow, that was really easy... I tried several time but each time selecting the wrong column, or the wrong option...
Is there any way to replace the null text with "" ?
When you load the results into the actual Power BI data model the null values should all load as blank. Are you literally getting the word "null" when you load?
You can force a blank by hitting the Replace Values button. Type null in the Value To Find box and just leave the Replace With box empty. But you shouldn't have to if they're truly null values and not the actual text "null".
Proud to be a Super User!
Correct. So everything is fine
Thank you very much
Pascal
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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
97 | |
77 | |
63 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |