Forum Discussion
Trying to overlay multiple pressure vs time data with different start times on the same visual
I have been at this for a few days and it's time to post. I have 5 datasets that have pressure and rate data per stage and my goal is to be able to overlay this data with a stage slicer. I have 5 wells that have 25 stages each, each well was completed on a different day so the time for each stage is unique. I have a Time column that is a per second parameter all 5 of the wells that is a per second perameter, I also have a "stageNo" which is the nuber of each stage in the well there are 25 per well. I want to be able to have a visual that I can hit the dropdown on a slicer that I select "StageNo" "1" and I can see the pressure and the rate over time for all 5 of the wells. The Date.Time is different for all 5 I need to have them all start at the orgin or a "time zero" and I need to create a relationship so they can be displayed on the same line chart. I have been through a very large volume of this forum trying to find an answer and I am in need of help.
Some information about the data (I will provide a sample)
-The StageNo counts up from 1 and is 1 untill the stage is finished and is then 2
-The elapsed time and time are continuous even throught the stageNo counting up
-I need a bridge table to link all 5 of the datasets
-I need a variable that I can use to plot the per second data of each stage at the same time
I have tried a portion of the following thread
In this thread I attempted to create a 0 and a 1 index and merged the querys and created a new index column that would sequentialy add +1 if the stage number stayed the same. I used that to make a dummy time column using DateTime.LocalNow() that added the sequential index column but couldn't bridge that between the datasets. I'm sorry this is all proprietary information so I might not be able to be as descriptive as I would like to be.
| Primary Location | JobNo | Time | Mainline Pressure (MPa) | Monitor Pressure (MPa) | Elapsed Time (min) | StageNo | Event # | Wellhead Clean Rate (m³/min) | Wellhead Clean Total per Event (m³) | Wellhead Clean Total per Stage (m³) | Wellhead Clean Total (m³) | Wellhead Slurry Rate (m³/min) | Wellhead Slurry Total per Event (m³) | Wellhead Slurry Total per Stage (m³) | Wellhead Slurry Total (m³) | Wellhead Proppant Conc. (kg/m³) | Combined Proppant Mass Rate (kg/min) | Combined Proppant Total Mass (tonne) | Combined Proppant Total Mass per Event (tonne) | Combined Proppant Total Mass per Stage (tonne) | Bottomhole Proppant Mass Rate (kg/min) | Bottomhole Proppant Total Mass (tonne) | Bottomhole Proppant Total Mass per Event (tonne) | Bottomhole Proppant Total Mass per Stage (tonne) | Bottomhole Proppant Conc. (kg/m³) | Index.0 | Index.1 | NewColumn.StageNo | NewIndex | StageDuration | StageDuration - Copy | StageDuration (Time) | Pad |
| Well 1 | 4713 | 2/29/2020 18:11 | 8.64941 | 5.79628 | 133.8028 | 2 | 9 | 0 | 0 | 0 | 38.94383 | 0 | 0 | 0 | 42.94413 | 0 | 0 | 9.99933 | 0 | 0 | 0 | 9.99933 | 0 | 0 | 0 | 8019 | 8020 | 1 | 8020 | 1 | 1 | 8:33:56 PM | TESTPAD |
| Well 1 | 4713 | 2/29/2020 18:11 | 7.95146 | 5.09825 | 133.8194 | 2 | 9 | 0 | 0 | 0 | 38.94383 | 0 | 0 | 0 | 42.94413 | 0 | 0 | 9.99933 | 0 | 0 | 0 | 9.99933 | 0 | 0 | 0 | 8020 | 8021 | 2 | 8020 | 2 | 2 | 8:33:57 PM | TESTPAD |
| Well 1 | 4713 | 2/29/2020 18:11 | 6.82956 | 3.67115 | 133.8361 | 2 | 9 | 0 | 0 | 0 | 38.94383 | 0 | 0 | 0 | 42.94413 | 0 | 0 | 9.99933 | 0 | 0 | 0 | 9.99933 | 0 | 0 | 0 | 8021 | 8022 | 2 | 8020 | 3 | 3 | 8:33:58 PM | TESTPAD |
| Well 1 | 4713 | 2/29/2020 18:11 | 6.33325 | 3.10239 | 133.8528 | 2 | 9 | 0 | 0 | 0 | 38.94383 | 0 | 0 | 0 | 42.94413 | 0 | 0 | 9.99933 | 0 | 0 | 0 | 9.99933 | 0 | 0 | 0 | 8022 | 8023 | 2 | 8020 | 4 | 4 | 8:33:59 PM | TESTPAD |
| Well 1 | 4713 | 2/29/2020 18:11 | 5.50605 | 1.88211 | 133.8696 | 2 | 9 | 0 | 0 | 0 | 38.94383 | 0 | 0 | 0 | 42.94413 | 0 | 0 | 9.99933 | 0 | 0 | 0 | 9.99933 | 0 | 0 | 0 | 8023 | 8024 | 2 | 8020 | 5 | 5 | 8:34:00 PM | TESTPAD |
| Well 1 | 4713 | 2/29/2020 18:11 | 4.79259 | 1.30301 | 133.8861 | 2 | 9 | 0 | 0 | 0 | 38.94383 | 0 | 0 | 0 | 42.94413 | 0 | 0 | 9.99933 | 0 | 0 | 0 | 9.99933 | 0 | 0 | 0 | 8024 | 8025 | 2 | 8020 | 6 | 6 | 8:34:01 PM | TESTPAD |
| Well 1 | 4713 | 2/29/2020 18:11 | 3.95505 | 1.03414 | 133.9028 | 2 | 9 | 0 | 0 | 0 | 38.94383 | 0 | 0 | 0 | 42.94413 | 0 | 0 | 9.99933 | 0 | 0 | 0 | 9.99933 | 0 | 0 | 0 | 8025 | 8026 | 2 | 8020 | 7 | 7 | 8:34:02 PM | TESTPAD |
#"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Stage #", "StageNo"}}),
#"Added Index0" = Table.AddIndexColumn(#"Renamed Columns", "Index.0", 0, 1),
#"Added Index1" = Table.AddIndexColumn(#"Added Index0", "Index.1", 1, 1),
#"Merged Queries" = Table.NestedJoin(#"Added Index1",{"Index.0"},#"Added Index1",{"Index.1"},"NewColumn",JoinKind.FullOuter),
#"Expanded NewColumn1" = Table.ExpandTableColumn(#"Merged Queries", "NewColumn", {"StageNo"}, {"NewColumn.StageNo"}),
#"Filtered Rows" = Table.SelectRows(#"Expanded NewColumn1", each ([NewColumn.StageNo] <> null)),
// If the item comparison doesn't match, then pull Index.1, else fill with null.
#"Added NewIndex" = Table.AddColumn(#"Filtered Rows", "NewIndex", each if [StageNo]<>[NewColumn.StageNo] then [Index.1] else null),
#"Filled Down to replace nulls in NewItem Index" = Table.FillDown(#"Added NewIndex",{"NewIndex"}),
#"Added StageDuration" = Table.AddColumn(#"Filled Down to replace nulls in NewItem Index", "StageDuration", each ([Index.1]-[NewIndex]+1), Int64.Type),
#"Duplicated Column1" = Table.DuplicateColumn(#"Added StageDuration", "StageDuration", "StageDuration - Copy"),
#"Changed Type1" = Table.TransformColumnTypes(#"Duplicated Column1",{{"StageDuration - Copy", type duration}}),
#"Replaced Value" = Table.ReplaceValue(#"Changed Type1",null,#duration(1, 0, 0, 0),Replacer.ReplaceValue,{"StageDuration - Copy"}),
#"Added Custom" = Table.AddColumn(#"Replaced Value", "StageDuration (Time)", each DateTime.LocalNow() + ([#"StageDuration - Copy"]/86400)),
#"Changed Type2" = Table.TransformColumnTypes(#"Added Custom",{{"StageDuration (Time)", type time}, {"StageNo", Int64.Type}}),
#"Added Custom1" = Table.AddColumn(#"Changed Type2", "Pad", each "TESTPAD", type text)
in
#"Added Custom1"
So I apoligise there is so much to unpack here, above is a sample of one of the wells data and the Query code I think I might have built a monster. Below is an example of what I would like to see sort of, this is one well with a number of stages and only Rate, I would like to see all 5 wells showing only one stage that I define with a dropdown slicer.
My main issues are
- Displaying each stageNo on the same plot while defining a x scale that works across all 5 wells
- Creating an active relationship between all 5 wells that will allow the ploting of the desired data
- the fact I am a total noob and I have to google everything.
1 Reply
- Greg_Deckler
Community Champion
A lot to unpack here. So, first question, can you append all of your 5 datasets together in a single table or have you done this already?