Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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 

https://community.powerbi.com/t5/Desktop/Adding-conditional-index-based-on-changing-field-in-Power-Query/td-p/436374

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 LocationJobNoTimeMainline Pressure (MPa)Monitor Pressure (MPa)Elapsed Time (min)StageNoEvent #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.0Index.1NewColumn.StageNoNewIndexStageDurationStageDuration - CopyStageDuration (Time)Pad
Well 147132/29/2020 18:118.649415.79628133.80282900038.9438300042.94413009.999330009.999330008019802018020118:33:56 PMTESTPAD
Well 147132/29/2020 18:117.951465.09825133.81942900038.9438300042.94413009.999330009.999330008020802128020228:33:57 PMTESTPAD
Well 147132/29/2020 18:116.829563.67115133.83612900038.9438300042.94413009.999330009.999330008021802228020338:33:58 PMTESTPAD
Well 147132/29/2020 18:116.333253.10239133.85282900038.9438300042.94413009.999330009.999330008022802328020448:33:59 PMTESTPAD
Well 147132/29/2020 18:115.506051.88211133.86962900038.9438300042.94413009.999330009.999330008023802428020558:34:00 PMTESTPAD
Well 147132/29/2020 18:114.792591.30301133.88612900038.9438300042.94413009.999330009.999330008024802528020668:34:01 PMTESTPAD
Well 147132/29/2020 18:113.955051.03414133.90282900038.9438300042.94413009.999330009.999330008025802628020778:34:02 PMTESTPAD

 

 

 

    #"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's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity 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?