Power Bi pivot in power query for percentage calculation

[SAPpowerbi]

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 .
Please help me to achive above output .

Thanks in advance .

[HotChilli]

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 ?

[SAPpowerbi]

ok Sure , thank you for the reply

[v-rzhou-msft · Accepted solution]

Hi @SAPpowerbi ,

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

Result is as below.

You can download my sample file to learn more details.

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.

[HotChilli]

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

--

Can you post the Advanced Editor code please?

[SAPpowerbi]

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 .

please help me .

[SAPpowerbi]

-

[HotChilli]

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

[SAPpowerbi]

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 .

so please help me for the power BI pivot Transformarion .

thank you