How to change two column Desc and amount into multiple columns each Desc for one column

[annecheng]

Can you help with this? I would like to change:   

It need group by org ID and Program ID and transfer program Fee desc and fee amount into different column for each Program Fee Desc

Org#	PROG ID	Program Fee Desc	Fee Amount
200002	1	Tuition Fees	10000
200021	1	Tuition Fees	9650
200021	1	Book Fees	980
200021	1	Professional and Exam Fee	450
200021	1	Uniform and Equipment	1200
200021	1	Student services	12280
200021	2	Tuition Fees	200
200021	2	Book Fees	600
200021	2	Professional and Exam Fee	50
200021	2	Uniform and Equipment	600
200021	2	Student services	50
200021	2	Other	40

to 

Org#	PROG ID	Tuition Fees	Book Fees	Major Equipment	Professional and Exam Fee	Uniform and Equipment	Other	Student services
200002	1	10000	0	0	0	0	0	0
200021	1	9650	980	0	450	1200	0	12280
200021	2	200	600	0	50	600	40	50

[v-angzheng-msft · Accepted solution]

 Hi, @annecheng 

Follow the steps below:

Sample:

Result:

Is this the result you want? Hope this is useful to you

Please feel free to let me know If you have further questions

Best Regards,
Community Support Team _ Zeon Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Ashish_Mathur]

Hi,

This M code works

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Org#", Int64.Type}, {"PROG ID", Int64.Type}, {"Program Fee Desc", type text}, {"Fee Amount", Int64.Type}}),
    #"Pivoted Column" = Table.Pivot(#"Changed Type", List.Distinct(#"Changed Type"[#"Program Fee Desc"]), "Program Fee Desc", "Fee Amount", List.Sum)
in
    #"Pivoted Column"

Hope this helps.

[TomMartens]

Hey @annecheng ,

you want to pivot the column "Program Fee Desc".
This article describes how you can achieve using Power Query: Pivot columns | Microsoft Docs

Hopefully, this is what you are looking for.

Regards,

Tom

[annecheng]

I updated the question to add one more layer for grouping.  Please help.  Thank you!