cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
PeterJansen2
Frequent Visitor

Pivot problem and mixed data

Hi,

 

I have a brainbuster (for me)

 

I have a table which I want to Pivot. All content is Text.

Pic1Pic1

I would like to Pivot the table to get the following result:

Pic2Pic2

This presents me with the following problems:

1) Pivot wont work because I have multiple rows with same EmplID and Code.

2) To solve that I thought of grouping, summerizing code 1000 and summerize code 2000. but that doesn't work because its is Text.

3) Converting Text to Numeric works only for the 1000 and 2000 code, which is all I need, but the other codes will present an error.

 

I think it I need multiple steps to solve this?

Split the Content column? (how?)

 

Any thoughts?

 

1 ACCEPTED SOLUTION
v-ljerr-msft
Microsoft
Microsoft

Hi @PeterJansen2,


 

3) Converting Text to Numeric works only for the 1000 and 2000 code, which is all I need, but the other codes will present an error.


If the other codes is not needed/necessary, you can replace the error values with null or 0(or any other values you want to show) after Converting Text to Numeric. Then you should be able use the Pivot option get your expected result. Smiley Happy

 

replceErrrors.PNGreplceErrrors2.PNG

 

Final result:

r1.PNG

 

Regards

View solution in original post

4 REPLIES 4
v-ljerr-msft
Microsoft
Microsoft

Hi @PeterJansen2,


 

3) Converting Text to Numeric works only for the 1000 and 2000 code, which is all I need, but the other codes will present an error.


If the other codes is not needed/necessary, you can replace the error values with null or 0(or any other values you want to show) after Converting Text to Numeric. Then you should be able use the Pivot option get your expected result. Smiley Happy

 

replceErrrors.PNGreplceErrrors2.PNG

 

Final result:

r1.PNG

 

Regards

chotu27
Post Patron
Post Patron

@PeterJansen2Use matrix Visual as below. will this work?Content.PNG

Thanks for your reply.

As far as I know, a visual won't do the trick. 

 

I need the pivotted data it in my tables, to do some calculations/measures etc with it.

 

 

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.