Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Generate a Table of Unique Segments by Start and End Point

I'm attempting to create a table of segments of a pipeline using data from PODS (Pipeline Open Data Standard).  Each record in the table will be a segment of the pipeline with unique features and start and end points (stations). Each feature type of the pipeline is contained within it's own table, with no direct relationship between other feature types besides start and end stations along the pipeline. The illustration below shows an example. A 50,000ft long pipeline has 3 feature types (diameter, test_pressure, and class) that need to be combined and segmented.  The features start and end at different stations, and in this example, the number of unique segments to be created is 6.

 

DynSeg Illustration2.JPG

 

The segments would be recorded as unique records in the table Dynamic_Segment. Below is an example showing the 6 records.

 

Dynamic_Segment

DYN_SEG_SEQBEGIN_STATIONEND_STATIONDIAMETERTEST_PRESSURECLASS
1010,00012900Class 1
210,00013,000121000Class 1
313,00016,000101000Class 1
416,00026,000101000Class 2
526,00031,000121000Class 2
631,00050,00012600Class 2

 

I'm not sure how to go about this in Power Query. In Excel, I would transform the data "by hand" combining 1 feature table to another, then using that combined table to compare against a 3rd feature table, and so on. I thought I'd be able to use VLOOKUP, but it's complicated because there can be multiple instances that match your criteria, and your criteria is a range of stations, not a 1:1 match that VLOOKUP is best used for. It can probably still be done in Excel, but it's outside of my skill set.

 

I've had success (small, limited) in Power Query combining columns, separating the data with a delimiter, then, using the "split columns to rows" feature in Power Query to generate new rows. It's a very helpful tool and I imagine it could be used to created these unique segments. Any helpful tips or comments or further reading is appreciated.  Thanks!



1 REPLY 1
Anonymous
Not applicable

Here's a list of the feature type reference tables as well that contain feature data and start/end stations.  Thanks! 

 

Route

ROUTE_SEQBEGIN_STATIONEND_STATION
1050,000

 

Pipe

ROUTE_SEQBEGIN_STATIONEND_STATIONDIAMETER
1013,00012
113,00026,00010
126,00050,00012

 

Pressure

ROUTE_SEQBEGIN_STATIONEND_STATIONTEST_PRESSURE
1010,000900
110,00031,0001000
131,00050,000600

 

Class

ROUTE_SEQBEGIN_STATIONEND_STATIONCLASS
1016,000Class 1
116,00050,000Class 2

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors