cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Helper II

## Power Bi pivot in power query for percentage calculation

Hi Team ,

I need to do pivot on below transformation ,please check source my output and expected output and help me for the same.

data information -

SL is A /B /C and clinet name is CG and TCS based on this i need to convert to pivot this is just a dummy data .

Source data -

 SL Clinet name date Value A CG Jan-23 10 B CG Jan-23 20 C CG Jan-23 30 A TCS Jan-23 40 B TCS Jan-23 50 C TCS Jan-23 60 A CG Feb-23 10 B CG Feb-23 20 C CG Feb-23 30 A TCS Feb-23 40 B TCS Feb-23 50 C TCS Feb-23 60

Output which I get - After pivot i get below output which is not expcted .

 Clinet Name date A B C CG Jan-23 10 Null Null TCS Jan-23 40 Null Null CG 23-Feb 10 Null Null TCS 23-Feb 40 Null Null CG Jan-23 Null 20 Null TCS Jan-23 Null 50 Null CG 23-Feb Null 20 Null TCS 23-Feb Null 50 Null CG Jan-23 Null Null 30 TCS Jan-23 Null Null 60 CG 23-Feb Null Null 30 TCS 23-Feb Null Null 60

Expected Output - I need ouput like below table -

 Clinet Name date A B C CG Jan-23 10 20 30 TCS Jan-23 40 50 60 CG 23-Feb 10 20 30 TCS 23-Feb 40 50 60

SO based on above ouput i will do calculated column like A/ (A+B+C)= Peracnatge .

1 ACCEPTED SOLUTION
Community Support

Hi @SAPpowerbi ,

I think you can click on [SL] column and then select [Value] column in Pivot function.

Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

8 REPLIES 8
Super User

The code looks absolutely fine.

However there are extra columns in the real data which are not in the sample further above.

Can you provide a picture (all columns please) or a realistic data sample of rows which you expect to Group but are not ?

Helper II

ok Sure , thank you for the reply

Community Support

Hi @SAPpowerbi ,

I think you can click on [SL] column and then select [Value] column in Pivot function.

Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Super User

I know it's sample data. I am explaining one possible reason why the Pivot has produced this result (which may be applicable to the original data).

--

Helper II

Please find above screenshot for your ref data look like this after pivot so M column values are showing now , but for C column once M column values are end then C column values are starting .

Helper II

-

Super User

It looks like the client names have spaces or special characters in them so they haven't grouped properly.

Can you Clean and Trim the columns before pivot please?

--

If it doesn't work, please post the Advanced Editor code and i'll have a look

Helper II

Hi ,
Thank you for Qucik reply , This is just a dummy data which i put here as a refrance - i have a origanal  data with coorect format .

thank you

Announcements

#### Fabric certifications survey

Certification feedback opportunity for the community.

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors