split/group multiple columns based on primary column

[jhenscheid]

I am trying to split out the ChargeType, BasisType, Rate and BillableTotal columns in to their own column so that each LoadID is only consuming one row.

For example, LoadID 52315053 would be on one row and the ChargeType, BasisType and Rate columns would be on the same row. 

The different options I have played around with are Pivot, Group and Transpose.  I am guessing I will need to use a dax calculation, but am not sure where to start.  

There are several columns between the LoadID and ChargeType, but they are all duplicated (similar to the BillableTotal and Distance columns).

See sample table at the bottom to see what I think the final results would look like.

Thanks in advance.

 Original sample table

LoadID	ChargeType	BasisType	Rate	BillableTotal	Distance
52315053	Base Detail	FLAT	283	328.44	142
52315053	Accessorial Detail	CPM	0.32	328.44	142
52323027	Base Detail	FLAT	345	410.88	366
52323027	Accessorial Detail	CPM	0.18	410.88	366
52323030	Base Detail	CPM	1.02	463.2	386
52323030	Accessorial Detail	CPM	0.18	463.2	386
52324114	Base Detail	FLAT	240	240	160
52324114	Accessorial Detail	CPM	0	240	160

Final Sample table.

LoadID	Base Type	BasisType	Rate	Accessorial	AccType	AccRate	BillableTotal	Distance
52315053	Base Detail	FLAT	283	Accessorial Detail	CPM	0.32	328.44	142
52323027	Base Detail	FLAT	345	Accessorial Detail	CPM	0.18	410.88	366
52323030	Base Detail	CPM	1.02	Accessorial Detail	CPM	0.18	463.2	386
52324114	Base Detail	FLAT	240	Accessorial Detail	CPM	0	240	160

[Vvelarde · Accepted solution]

@jhenscheid

Hi, Please try this in Query Editor:

Steps in Query Editor

Regards

Victor

Lima - Peru

[jhenscheid]

Thank you for your help.  This worked grreat.  I actually had another table with a similar layout and used the steps for that table as well.

thanks again!