Make New Columns based on Cell Value to Flatten and Retain Data

[anelson]

I would like to flatten my data to just 1 row per Load Number.  But I have to make a new column for Stopoff and for Final Delivery locations.  Stopoff is notated by a D in Stop Type, and a Stop ID of 1 (sometimes there are multiple Stopoffs, with Stop IDs increasing in increments of 1 - ex. 1, 2, 3, 4).  Final Delivery is always notated by a D in Stop Type and a Stop ID of 999.

I have shown below a simplified example.  I will have to scale up the solution to make new columns for all relevant information of each location.

Thank you for any help!  First time posting...if there's a better way to ask questions going forward, please advise.

Aaron

Load Number	Stop Type	Stop ID	Location Type
963345	P	0	Shipper
963345	D	1	Stopoff
963345	D	999	Final Delivery
963346	P	0	Shipper
963346	D	1	Stopoff
963346	D	999	Final Delivery
963347	P	0	Shipper
963347	D	999	Final Delivery

[v-yuezhe-msft · Accepted solution]

@anelson,

Please check if Merge2  table returns your expected result in the following PBIX file.

https://1drv.ms/u/s!AhsotbnGu1NolAADVkhaKAdrTRpu

Regards,
Lydia

[anelson]

Yes!  That's exactly what I need it to do. 

For the record, this is what Lydia did:

1. Duplicated original data table
2. Made the new tables filtered to one type of location (so now have tables with just "stopoff", "shipper", and "final delivery".
3. Merged these queries into a new table (have to do twice to get all 3 types in one query).
4. Expand the new columns!

Thank you Lydia!