I have a 6 column table and need to transpose two columns and eliminate duplicate rows.

[ChaChaBones]

My first post.  Hope I do it right!

I have the data below in the "Current Data" table and I need it to be arranged like the data in the "Desired Data" table below it.  The original is coming from sql and I'm using the final data in Excel.  I can do it in Excel with a pivot table but there is vastly more data than I'm representing here and I'd rather get it done in Power Query so I can update the data without manipulate it every month.  Thanks in advance.

Current Data:

Date	Customer#	SO#	Inv#	RptGrp	Amount
06/25/24	Customer 01	OLSSO001	OLSINV004	001_Rev	6,491.00
06/25/24	Customer 01	OLSSO001	OLSINV004	003_Cost	313.48
06/25/24	Customer 01	OLSSO001	OLSINV004	004_DSRev	1,500.00
06/25/24	Customer 01	OLSSO001	OLSINV004	005_DSCost	1,050.00
06/04/24	Customer 02	SO06218	STDINV06043	001_Rev	386.20
06/04/24	Customer 02	SO06218	STDINV06043	003_Cost	113.88
06/04/24	Customer 02	SO06218	STDINV06043	004_DSRev	950.00
06/04/24	Customer 02	SO06218	STDINV06043	005_DSCost	700.00

Desired table:

Date	Customer#	SO#	Inv#	001_Rev	003_Cost	004_DSRev	005_DSCost
06/25/24	Customer 01	OLSSO001	OLSINV004	6,491.00	313.48	1500	1050
06/04/24	Customer 02	SO06218	STDINV06043	386.20	113.88	950	700

[lbendlin · Accepted solution]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("pZDLCsIwEEV/RbIO6Z28TNftRhALVtyUklWXUrDV73eCVavLuLszDIc7p+sEfKFdoa2QorpN83gZrhsQT82+bRu84u5wBtIRb+JxuHPy0pakANHLHIyJ1TjNHA0ZZUMmxca6fdYh6YD8Oo5BSyGScGsS7C9J88QYrymkdKoTyMOaL0EmeKWzKW8/xH5CyMV8BJV/PbXys1089w8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, #"Customer#" = _t, #"SO#" = _t, #"Inv#" = _t, RptGrp = _t, Amount = _t]),
    #"Pivoted Column" = Table.Pivot(Source, List.Distinct(Source[RptGrp]), "RptGrp", "Amount")
in
    #"Pivoted Column"

[ChaChaBones]

Thank you both for your solutions as they were both helpful.  However, at this point, I believe the first solution from @lbendlin  comes closer to fully solving my problem.  The lack of a complete solution at this point is my fault because my original test data was not complete on two fronts.

First, I simplified the original test data and now realize I over-simplified it so it was not complete. 

Second, my data source is actually from a SQL View and I don't know how to adjust the syntax in the first solution to accomodate for that.

I tried to tweak the first solution to accomodate for my two misfires but I couldn't get it to work.   I will provided beter data shortly.

[dufoq3]

Just for your information: both solutions are exactly the same 😉

[lbendlin · Accepted solution]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("pZDLCsIwEEV/RbIO6Z28TNftRhALVtyUklWXUrDV73eCVavLuLszDIc7p+sEfKFdoa2QorpN83gZrhsQT82+bRu84u5wBtIRb+JxuHPy0pakANHLHIyJ1TjNHA0ZZUMmxca6fdYh6YD8Oo5BSyGScGsS7C9J88QYrymkdKoTyMOaL0EmeKWzKW8/xH5CyMV8BJV/PbXys1089w8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, #"Customer#" = _t, #"SO#" = _t, #"Inv#" = _t, RptGrp = _t, Amount = _t]),
    #"Pivoted Column" = Table.Pivot(Source, List.Distinct(Source[RptGrp]), "RptGrp", "Amount")
in
    #"Pivoted Column"

[dufoq3 · Accepted solution]

Hi @ChaChaBones, graphical solution by @lbendlin: