Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
PascalT
Helper I
Helper I

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

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].

 

EmailAddressKeyValue
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.itAppleItaly23/12/2016+39 2323 2323
pascal@ibm.dkIBMDenmark  
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

2 ACCEPTED SOLUTIONS
KHorseman
Community Champion
Community Champion

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!

 

 

Step 1Step 1

 

 

Step 2Step 2

 

ResultsResults





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

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".





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

6 REPLIES 6
KHorseman
Community Champion
Community Champion

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!

 

 

Step 1Step 1

 

 

Step 2Step 2

 

ResultsResults





Did I answer your question? Mark my post as a solution!

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".





Did I answer your question? Mark my post as a solution!

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.

 

TC-1.PNG

 

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

 

TC-2.PNG

 

> And then i got this:

 

TC-3.PNG

 

> And the error is:

 

TC-4.PNG

 

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.

 

TC-1.PNG

 

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

 

TC-2.PNG

 

> And then i got this:

 

TC-3.PNG

 

> And the error is:

 

TC-4.PNG

 

Do I do something wrong ? 

 

Thank you very Much

 

Pascal

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.