Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

The Power BI Data Visualization World Championships is back! Get ahead of the game and start preparing now! Learn more

Reply
hanuraolm
Helper I
Helper I

rederive StartDTNew, EndDTNew based on existing StartDT and EndDT attributes

‎09-13-2023 10:36 AM

Need to rederive StartDTNew, EndDTNew based on existing StartDT and EndDT - For each Date, Tool & Module.  Main purpose of this solution is needed to calculate the TotalHours occupied for each Tool,Module, Day. 

 

Source data is generated with Overlap dates, so I thought if we shifting startDT, EndDate as below records are highlated (, may be we can able to derive the utilized hours. If any solution in M language or DAX is highly appreciated.

 

DateToolModuleStartDTEndDTStartDTNewEndDTNewEx - formula for StartDTNewEx - formula for EndDTNew
8/24/2023Lab1PM28/24/2023 10:26:02 AM8/24/2023 10:28:29 AM8/24/2023 10:26:02 AM8/24/2023 10:28:29 AMStartDT<EndDT then StartDT else EndDTEndDT
8/24/2023Lab1PM28/24/2023 10:26:02 AM8/24/2023 10:28:29 AM8/24/2023 10:28:29 AM8/24/2023 10:28:29 AMStartDT<PreviousRecordEndDT then PreviousRecordEndDT else StartDTEndDT>StartDTNew then EndDT else StartDTNew
8/24/2023Lab1PM28/24/2023 10:26:02 AM8/24/2023 10:28:29 AM8/24/2023 10:28:29 AM8/24/2023 10:28:29 AMStartDT<PreviousRecordEndDT then PreviousRecordEndDT else StartDTEndDT>StartDTNew then EndDT else StartDTNew
8/24/2023Lab1PM28/24/2023 10:28:30 AM8/24/2023 10:29:37 AM8/24/2023 10:28:30 AM8/24/2023 10:29:37 AMStartDT<PreviousRecordEndDT then PreviousRecordEndDT else StartDTEndDT>StartDTNew then EndDT else StartDTNew
8/24/2023Lab1PM28/24/2023 10:29:47 AM8/24/2023 10:35:36 AM8/24/2023 10:29:47 AM8/24/2023 10:35:36 AMStartDT<PreviousRecordEndDT then PreviousRecordEndDT else StartDTEndDT>StartDTNew then EndDT else StartDTNew
8/24/2023Lab1PM28/24/2023 10:29:47 AM8/24/2023 10:35:36 AM8/24/2023 10:35:36 AM8/24/2023 10:35:36 AMStartDT<PreviousRecordEndDT then PreviousRecordEndDT else StartDTEndDT>StartDTNew then EndDT else StartDTNew
8/24/2023Lab1PM28/24/2023 10:30:11 AM8/24/2023 10:35:13 AM8/24/2023 10:35:36 AM8/24/2023 10:35:36 AMStartDT<PreviousRecordEndDT then PreviousRecordEndDT else StartDTEndDT>StartDTNew then EndDT else StartDTNew
8/24/2023Lab2PM18/24/2023 10:30:11 AM8/24/2023 10:35:13 AM8/24/2023 10:30:11 AM8/24/2023 10:35:13 AMStartDT<EndDT then StartDT else EndDTEndDT
8/24/2023Lab2PM18/24/2023 10:26:02 AM8/24/2023 10:28:29 AM8/24/2023 10:35:13 AM8/24/2023 10:35:13 AMStartDT<PreviousRecordEndDT then PreviousRecordEndDT else StartDTEndDT>StartDTNew then EndDT else StartDTNew
8/24/2023Lab2PM18/24/2023 10:26:02 AM8/24/2023 10:28:29 AM8/24/2023 10:35:13 AM8/24/2023 10:35:13 AMStartDT<PreviousRecordEndDT then PreviousRecordEndDT else StartDTEndDT>StartDTNew then EndDT else StartDTNew
8/24/2023Lab2PM18/24/2023 10:28:30 AM8/24/2023 10:29:37 AM8/24/2023 10:35:13 AM8/24/2023 10:35:13 AMStartDT<PreviousRecordEndDT then PreviousRecordEndDT else StartDTEndDT>StartDTNew then EndDT else StartDTNew
8/24/2023Lab2PM18/24/2023 10:29:47 AM8/24/2023 10:35:36 AM8/24/2023 10:35:13 AM8/24/2023 10:35:36 AMStartDT<PreviousRecordEndDT then PreviousRecordEndDT else StartDTEndDT>StartDTNew then EndDT else StartDTNew
8/24/2023Lab2PM18/24/2023 10:29:47 AM8/24/2023 10:35:36 AM8/24/2023 10:35:36 AM8/24/2023 10:35:36 AMStartDT<PreviousRecordEndDT then PreviousRecordEndDT else StartDTEndDT>StartDTNew then EndDT else StartDTNew
8/24/2023Lab2PM18/24/2023 10:30:11 AM8/24/2023 10:35:13 AM8/24/2023 10:35:36 AM8/24/2023 10:35:36 AMStartDT<PreviousRecordEndDT then PreviousRecordEndDT else StartDTEndDT>StartDTNew then EndDT else StartDTNew
8/24/2023Lab2PM18/24/2023 10:30:11 AM8/24/2023 10:35:13 AM8/24/2023 10:35:36 AM8/24/2023 10:35:36 AMStartDT<PreviousRecordEndDT then PreviousRecordEndDT else StartDTEndDT>StartDTNew then EndDT else StartDTNew
Labels:
Message 1 of 11
1,657 Views
0
3 ACCEPTED SOLUTIONS
ImkeF
Community Champion
Community Champion

‎10-07-2023 01:24 PM

Hi @hanuraolm ,

sorry I didn't get the problem right with my first answer.
The main reason your attempt is terribly slow is probably due to the "PreviousRows"-step.
(if you need a fast way to retrieve the previous row, check out my post here: Fast and easy way to reference previous or next rows in Power Query or Power BI – (thebiccountant.co...)


My solution now returns 100k rows in under 10 seconds, so this should be good for your data hopefully as well 😉

let
  Source = SourceData,
  #"Added Index1" = Table.AddIndexColumn(Source, "Index", 0, 1, Int64.Type),
  fnShifts = (SourceTable as table) =>
    let
      InputTable = Table.Buffer(SourceTable),
      Custom1 = List.Generate(
        () => [
          StartDT    = InputTable[StartDT]{0},
          EndDT      = InputTable[EndDT]{0},
          NewStartDT = if StartDT < EndDT then StartDT else EndDT,
          NewEndDT   = EndDT,
          Counter    = 0
        ],
        each [Counter] < Table.RowCount(InputTable),
        each [
          Counter    = [Counter] + 1,
          StartDT    = InputTable[StartDT]{Counter},
          EndDT      = InputTable[EndDT]{Counter},
          NewStartDT = if StartDT < [NewEndDT] then [NewEndDT] else StartDT,
          NewEndDT   = if EndDT > NewStartDT then EndDT else NewStartDT
        ]
      )
    in
      Custom1,
  #"Grouped Rows" = Table.Group(
    #"Added Index1",
    {"Date", "Tool", "Module"},
    {{"All", each fnShifts(_)}}
  ),
  #"Expanded All" = Table.ExpandListColumn(#"Grouped Rows", "All"),
  #"Expanded All1" = Table.ExpandRecordColumn(
    #"Expanded All",
    "All",
    {"NewStartDT", "NewEndDT", "Counter", "StartDT", "EndDT"}
  ),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded All1",{{"NewStartDT", type datetime}, {"NewEndDT", type datetime}, {"StartDT", type datetime}, {"EndDT", type datetime}})
in
  #"Changed Type"

Also, check the file attached.


Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

Preview file
3162 KB
Message 5 of 11
1,494 Views
0
ImkeF
Community Champion
Community Champion

‎10-08-2023 12:04 AM

Hi @hanuraolm ,
you are picking the wrong table here.
My table is "ImkeResult".
Please also check columns AT-AX where I reconciled the results agains the original requirement.
If you spot differences, please provide screenshots that include all relevant columns so that we can identify what is actually going on.  

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

Message 7 of 11
1,478 Views
ImkeF
Community Champion
Community Champion

‎10-08-2023 08:26 AM

Hi @hanuraolm ,
very pleased to hear 🙂

You can adjust like so:

***Grouping Logic****

#"Grouped Rows" = Table.Group(

    #"Added Index1",

    {"Date", "Tool", "Module"},

    {{"All", each fnShifts(_)}}

  ),

 

*****Function Definition****

    let

      InputTable = Table.Buffer(SourceTable),

      Custom1 = List.Generate(

        () => [

          StartDT    = InputTable[StartDT]{0},

          EndDT      = InputTable[EndDT]{0},

          NewStartDT = if StartDT < EndDT then StartDT else EndDT,

          NewEndDT   = EndDT,

          Counter    = 0

          Label = InputTable[sourcelabel]{0},

          Recipe = InputTable[SourceRecipe]{0}

        ],

        each [Counter] < Table.RowCount(InputTable),

        each [

          Counter    = [Counter] + 1,

          StartDT    = InputTable[StartDT]{Counter},

          EndDT      = InputTable[EndDT]{Counter},

          NewStartDT = if StartDT < [NewEndDT] then [NewEndDT] else StartDT,

          NewEndDT   = if EndDT > NewStartDT then EndDT else NewStartDT

          Label = InputTable[sourcelabel]{Counter},

          Recipe = InputTable[SourceRecipe]{Counter}

 

        ]

      )

    in

      Custom1,

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

Message 9 of 11
1,456 Views
0
10 REPLIES 10
ImkeF
Community Champion
Community Champion

‎10-08-2023 09:13 AM

Hi @hanuraolm ,
yes, you gave it the name "newlot" and that's what you have to expand and transform further on 🙂

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Message 11 of 11
1,448 Views
0
ImkeF
Community Champion
Community Champion

‎10-08-2023 08:26 AM

Hi @hanuraolm ,
very pleased to hear 🙂

You can adjust like so:

***Grouping Logic****

#"Grouped Rows" = Table.Group(

    #"Added Index1",

    {"Date", "Tool", "Module"},

    {{"All", each fnShifts(_)}}

  ),

 

*****Function Definition****

    let

      InputTable = Table.Buffer(SourceTable),

      Custom1 = List.Generate(

        () => [

          StartDT    = InputTable[StartDT]{0},

          EndDT      = InputTable[EndDT]{0},

          NewStartDT = if StartDT < EndDT then StartDT else EndDT,

          NewEndDT   = EndDT,

          Counter    = 0

          Label = InputTable[sourcelabel]{0},

          Recipe = InputTable[SourceRecipe]{0}

        ],

        each [Counter] < Table.RowCount(InputTable),

        each [

          Counter    = [Counter] + 1,

          StartDT    = InputTable[StartDT]{Counter},

          EndDT      = InputTable[EndDT]{Counter},

          NewStartDT = if StartDT < [NewEndDT] then [NewEndDT] else StartDT,

          NewEndDT   = if EndDT > NewStartDT then EndDT else NewStartDT

          Label = InputTable[sourcelabel]{Counter},

          Recipe = InputTable[SourceRecipe]{Counter}

 

        ]

      )

    in

      Custom1,

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Message 9 of 11
1,457 Views
0
hanuraolm
Helper I
Helper I
In response to ImkeF

‎12-11-2023 03:19 AM
Hi ImkeF - 
 
Your code is very helpful. I need to define two more columns as recordNewStartDT,recordNewEndDT. Logic as below.
 

1. group by StartDate,Site,Tool & Module
2. Need to derive recordNewStartDT, recordNewEndDT
3. recordNewStartDT is same as NewStartDT, which is derived using fnShifts function. (Code is below)
4. recordNewEndDT - this value should pickup from "NewStartDT" in the next row in a group (StartDate,Site,Tool & Module) and For the last row in this group there is no value to pickup, so recordNewEndDT is same vaue from NewStartDT.

 

the above loop should be repeted for each group. Is this can we manage in the same fuction, or else do we need to go other fucntion to derive these columns. Your inputs are heighly appreciated.

 
 
#"Sorted Rows" = Table.Sort(#"Reordered Columns",{{"StartDate", Order.Ascending}, {"Site", Order.Ascending}, {"Tool", Order.Ascending}, {"Module", Order.Ascending}, {"StartDateTime", Order.Ascending}}),
  #"Added Index1" = Table.AddIndexColumn(#"Sorted Rows", "Index", 0, 1, Int64.Type),
  fnShifts = (SourceTable as table) =>
    let
      InputTable = Table.Buffer(SourceTable),
      Custom1 = List.Generate(
        () => [
          StartDT    = InputTable[StartDateTime]{0},
          EndDT      = InputTable[EndDateTime]{0},
          NewStartDT = if StartDT < EndDT then StartDT else EndDT,
          NewEndDT   = EndDT,
          Counter    = 0,
          lot_ID = InputTable[lot_ID]{0},
      recipe_ID = InputTable[recipe_ID]{0},
          FileName = InputTable[FileName]{0}
        ],
        each [Counter] < Table.RowCount(InputTable),
        each [
          Counter    = [Counter] + 1,
          StartDT    = InputTable[StartDateTime]{Counter},
          EndDT      = InputTable[EndDateTime]{Counter},
          NewStartDT = if StartDT < [NewEndDT] then [NewEndDT] else StartDT,
          NewEndDT   = if EndDT > NewStartDT then EndDT else NewStartDT,
      lot_ID = InputTable[lot_ID]{Counter},
      recipe_ID = InputTable[recipe_ID]{Counter},
          FileName = InputTable[FileName]{Counter}
        ]
      )
    in
      Custom1,
  #"Grouped Rows" = Table.Group(
    #"Added Index1",
    {"StartDate", "Site", "Tool", "Module"},
    {{"All", each fnShifts(_)}}
  ),
  #"Expanded All" = Table.ExpandListColumn(#"Grouped Rows", "All")
 
 
hanuraolm_0-1702293475010.png

 

Message 10 of 11
1,279 Views
0
hanuraolm
Helper I
Helper I

‎10-08-2023 07:53 AM

Thanks, you so much and I really appreciate your work. This code is working as expected with super speed.😀

 

Small change is required without changing the below grouping logic we need to display other attributes. Like Chamber,recipe..etc how to get those or else we need to add in in fuction definition itself as below??

 

***Grouping Logic****

#"Grouped Rows" = Table.Group(

    #"Added Index1",

    {"Date", "Tool", "Module"},

    {{"All", each fnShifts(_)}}

  ),

 

*****Function Definition****

    let

      InputTable = Table.Buffer(SourceTable),

      Custom1 = List.Generate(

        () => [

          StartDT    = InputTable[StartDT]{0},

          EndDT      = InputTable[EndDT]{0},

          NewStartDT = if StartDT < EndDT then StartDT else EndDT,

          NewEndDT   = EndDT,

          Counter    = 0

          Label = sourcelabel

          Recipe = SourceRecipe

        ],

        each [Counter] < Table.RowCount(InputTable),

        each [

          Counter    = [Counter] + 1,

          StartDT    = InputTable[StartDT]{Counter},

          EndDT      = InputTable[EndDT]{Counter},

          NewStartDT = if StartDT < [NewEndDT] then [NewEndDT] else StartDT,

          NewEndDT   = if EndDT > NewStartDT then EndDT else NewStartDT

          Label = sourcelabel

          Recipe = SourceRecipe

 

        ]

      )

    in

      Custom1,

Message 8 of 11
1,465 Views
0
ImkeF
Community Champion
Community Champion

‎10-08-2023 12:04 AM

Hi @hanuraolm ,
you are picking the wrong table here.
My table is "ImkeResult".
Please also check columns AT-AX where I reconciled the results agains the original requirement.
If you spot differences, please provide screenshots that include all relevant columns so that we can identify what is actually going on.  

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Message 7 of 11
1,479 Views
hanuraolm
Helper I
Helper I

‎10-07-2023 11:27 PM

Thanks so much for you help. It’s not just previou row to pick-up.

Logic as below

Sort StarDT Asc

Group Date,tool, module For each group, may we we can generate index to refer

then Shift the StartDT, EndDT in Order for each group (or ex see the targeted output below)

hanuraolm_0-1696746306621.png

 

Your code is producing as below

hanuraolm_1-1696746330690.png

 

 

Message 6 of 11
1,486 Views
0
ImkeF
Community Champion
Community Champion

‎10-07-2023 01:24 PM

Hi @hanuraolm ,

sorry I didn't get the problem right with my first answer.
The main reason your attempt is terribly slow is probably due to the "PreviousRows"-step.
(if you need a fast way to retrieve the previous row, check out my post here: Fast and easy way to reference previous or next rows in Power Query or Power BI – (thebiccountant.co...)


My solution now returns 100k rows in under 10 seconds, so this should be good for your data hopefully as well 😉

let
  Source = SourceData,
  #"Added Index1" = Table.AddIndexColumn(Source, "Index", 0, 1, Int64.Type),
  fnShifts = (SourceTable as table) =>
    let
      InputTable = Table.Buffer(SourceTable),
      Custom1 = List.Generate(
        () => [
          StartDT    = InputTable[StartDT]{0},
          EndDT      = InputTable[EndDT]{0},
          NewStartDT = if StartDT < EndDT then StartDT else EndDT,
          NewEndDT   = EndDT,
          Counter    = 0
        ],
        each [Counter] < Table.RowCount(InputTable),
        each [
          Counter    = [Counter] + 1,
          StartDT    = InputTable[StartDT]{Counter},
          EndDT      = InputTable[EndDT]{Counter},
          NewStartDT = if StartDT < [NewEndDT] then [NewEndDT] else StartDT,
          NewEndDT   = if EndDT > NewStartDT then EndDT else NewStartDT
        ]
      )
    in
      Custom1,
  #"Grouped Rows" = Table.Group(
    #"Added Index1",
    {"Date", "Tool", "Module"},
    {{"All", each fnShifts(_)}}
  ),
  #"Expanded All" = Table.ExpandListColumn(#"Grouped Rows", "All"),
  #"Expanded All1" = Table.ExpandRecordColumn(
    #"Expanded All",
    "All",
    {"NewStartDT", "NewEndDT", "Counter", "StartDT", "EndDT"}
  ),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded All1",{{"NewStartDT", type datetime}, {"NewEndDT", type datetime}, {"StartDT", type datetime}, {"EndDT", type datetime}})
in
  #"Changed Type"

Also, check the file attached.


Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Preview file
3162 KB
Message 5 of 11
1,495 Views
0
hanuraolm
Helper I
Helper I

‎10-07-2023 07:03 AM

thank for you help. But  i'm looking something as below, need to derived newStartDT, newEndDT based on avaible startDT, endDT for each day,tool, module 

source- 

DateToolModuleStartDTEndDT

8/24/2023Lab1PM28/24/2023 10:26:02 AM8/24/2023 11:28:29 AM
8/24/2023Lab1PM28/24/2023 10:36:02 AM8/24/2023 10:46:02 AM
8/24/2023Lab1PM28/24/2023 11:38:29 AM8/24/2023 11:40:29 AM
8/24/2023Lab1PM28/24/2023 10:36:02 AM8/24/2023 10:56:02 AM
8/24/2023Lab1PM28/24/2023 10:56:02 PM8/24/2023 11:56:02 PM
8/24/2023Lab1PM38/24/2023 9:26:02 AM8/24/2023 10:28:29 AM
8/24/2023Lab1PM38/24/2023 9:36:02 AM8/24/2023 9:46:02 AM
8/24/2023Lab1PM38/24/2023 10:38:29 AM8/24/2023 10:40:29 AM
8/24/2023Lab1PM38/24/2023 9:36:02 AM8/24/2023 9:56:02 AM
8/24/2023Lab1PM38/24/2023 9:56:02 PM8/24/2023 10:56:02 PM
8/25/2023Lab1PM28/25/2023 10:26:02 AM8/25/2023 11:28:29 AM
8/25/2023Lab1PM28/25/2023 10:36:02 AM8/25/2023 10:46:02 AM
8/25/2023Lab1PM28/25/2023 11:38:29 AM8/25/2023 11:40:29 AM
8/25/2023Lab1PM28/25/2023 10:36:02 AM8/25/2023 10:56:02 AM
8/25/2023Lab1PM28/25/2023 10:56:02 PM8/25/2023 11:56:02 PM
8/25/2023Lab1PM38/25/2023 9:26:02 AM8/25/2023 10:28:29 AM
8/25/2023Lab1PM38/25/2023 9:36:02 AM8/25/2023 9:46:02 AM
8/25/2023Lab1PM38/25/2023 10:38:29 AM8/25/2023 10:40:29 AM
8/25/2023Lab1PM38/25/2023 9:36:02 AM8/25/2023 9:56:02 AM
8/25/2023Lab1PM38/25/2023 9:56:02 PM8/25/2023 10:56:02 PM

 

Target

DateToolModuleIndexStartDT - CopyEndDT - CopynewStartDTnewEndDT

8/24/2023Lab1PM208/24/2023 10:26:02 AM8/24/2023 11:28:29 AM8/24/2023 10:26:02 AM8/24/2023 11:28:29 AM
8/24/2023Lab1PM218/24/2023 10:36:02 AM8/24/2023 10:46:02 AM8/24/2023 11:28:29 AM8/24/2023 11:28:29 AM
8/24/2023Lab1PM228/24/2023 10:36:02 AM8/24/2023 10:56:02 AM8/24/2023 11:28:29 AM8/24/2023 11:28:29 AM
8/24/2023Lab1PM238/24/2023 11:38:29 AM8/24/2023 11:40:29 AM8/24/2023 11:38:29 AM8/24/2023 11:40:29 AM
8/24/2023Lab1PM248/24/2023 10:56:02 PM8/24/2023 11:56:02 PM8/24/2023 10:56:02 PM8/24/2023 11:56:02 PM
8/24/2023Lab1PM308/24/2023 9:26:02 AM8/24/2023 10:28:29 AM8/24/2023 9:26:02 AM8/24/2023 10:28:29 AM
8/24/2023Lab1PM318/24/2023 9:36:02 AM8/24/2023 9:46:02 AM8/24/2023 10:28:29 AM8/24/2023 10:28:29 AM
8/24/2023Lab1PM328/24/2023 9:36:02 AM8/24/2023 9:56:02 AM8/24/2023 10:28:29 AM8/24/2023 10:28:29 AM
8/24/2023Lab1PM338/24/2023 10:38:29 AM8/24/2023 10:40:29 AM8/24/2023 10:38:29 AM8/24/2023 10:40:29 AM
8/24/2023Lab1PM348/24/2023 9:56:02 PM8/24/2023 10:56:02 PM8/24/2023 9:56:02 PM8/24/2023 10:56:02 PM
8/25/2023Lab1PM208/25/2023 10:26:02 AM8/25/2023 11:28:29 AM8/25/2023 10:26:02 AM8/25/2023 11:28:29 AM
8/25/2023Lab1PM218/25/2023 10:36:02 AM8/25/2023 10:46:02 AM8/25/2023 11:28:29 AM8/25/2023 11:28:29 AM
8/25/2023Lab1PM228/25/2023 10:36:02 AM8/25/2023 10:56:02 AM8/25/2023 11:28:29 AM8/25/2023 11:28:29 AM
8/25/2023Lab1PM238/25/2023 11:38:29 AM8/25/2023 11:40:29 AM8/25/2023 11:38:29 AM8/25/2023 11:40:29 AM
8/25/2023Lab1PM248/25/2023 10:56:02 PM8/25/2023 11:56:02 PM8/25/2023 10:56:02 PM8/25/2023 11:56:02 PM
8/25/2023Lab1PM308/25/2023 9:26:02 AM8/25/2023 10:28:29 AM8/25/2023 9:26:02 AM8/25/2023 10:28:29 AM
8/25/2023Lab1PM318/25/2023 9:36:02 AM8/25/2023 9:46:02 AM8/25/2023 10:28:29 AM8/25/2023 10:28:29 AM
8/25/2023Lab1PM328/25/2023 9:36:02 AM8/25/2023 9:56:02 AM8/25/2023 10:28:29 AM8/25/2023 10:28:29 AM
8/25/2023Lab1PM338/25/2023 10:38:29 AM8/25/2023 10:40:29 AM8/25/2023 10:38:29 AM8/25/2023 10:40:29 AM
8/25/2023Lab1PM348/25/2023 9:56:02 PM8/25/2023 10:56:02 PM8/25/2023 9:56:02 PM8/25/2023 10:56:02 PM

 

 

Code Executed - 

let
Source = Excel.Workbook(File.Contents("C:\Users\xxxxx\Downloads\Sample Data.xlsx"), null, true),
Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
#"Grouped Rows" = Table.Group(#"Promoted Headers", {"Date", "Tool", "Module"}, {{"fff", each Table.Sort(_,{{"StartDT", Order.Ascending}}), type table [Date=date, Tool=text, Module=text, StartDT=datetime, EndDT=datetime]}}),
#"Added Index" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([fff], "Index", 0)),
#"Removed Other Columns" = Table.SelectColumns(#"Added Index", {"Custom"}),
#"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom", {"Date", "Tool", "Module", "StartDT", "EndDT", "Index"}, {"Date", "Tool", "Module", "StartDT", "EndDT", "Index"}),
#"Duplicated Column -StartDT" = Table.DuplicateColumn(#"Expanded Custom", "StartDT", "StartDT - Copy"),
#"Duplicated Column - EndDT" = Table.DuplicateColumn(#"Duplicated Column -StartDT", "EndDT", "EndDT - Copy"),
#"Unpivoted Columns - Attribute" = Table.UnpivotOtherColumns(#"Duplicated Column - EndDT", {"Date", "Tool", "Module", "Index", "StartDT - Copy", "EndDT - Copy"}, "Attribute", "Value"),
#"Grouped Rows1" = Table.Group(#"Unpivoted Columns - Attribute", {"Date", "Tool", "Module"}, {{"all", each _, type table [Date=date, Tool=text, Module=text, Index=number, Value=datetime]}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows1", "Custom1", each Table.AddIndexColumn([all], "Index1", 0)),
#"Removed Other Columns1" = Table.SelectColumns(#"Added Custom", {"Custom1"}),
#"Expanded Custom1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Custom1", {"Date", "Tool", "Module", "Index", "StartDT - Copy", "EndDT - Copy", "Attribute", "Value", "Index1"}, {"Date", "Tool", "Module", "Index", "StartDT - Copy", "EndDT - Copy", "Attribute", "Value", "Index1"}),

// Define a custom function to calculate the maximum value from previous rows within the same Date, Tool, and Module group
CalculateMaxValue = (table as table, index as number, dateValue as date, toolValue as text, moduleValue as text) =>
let
// Buffer the table to improve performance
bufferedTable = Table.Buffer(#"Expanded Custom1"),
// Filter the buffered table to select relevant rows
previousRows = Table.SelectRows(bufferedTable, each [Index1] < index and [Date] = dateValue and [Tool] = toolValue and [Module] = moduleValue),
// Calculate the maximum using List.Max
max = List.Max(previousRows[Value])
in
max,

// Add the "New StartDateTime" column using the custom function
#"AddNewStartDateTime" = Table.AddColumn(#"Expanded Custom1", "NewValue", each if [Index1] = 0 then [Value] else if [Value] >= CalculateMaxValue(#"Expanded Custom1", [Index1], [Date], [Tool], [Module]) then [Value] else CalculateMaxValue(#"Expanded Custom1", [Index1], [Date], [Tool], [Module])),
#"Pivoted Column" = Table.Pivot(AddNewStartDateTime, List.Distinct(AddNewStartDateTime[Attribute]), "Attribute", "NewValue"),
#"Grouped Rows2" = Table.Group(#"Pivoted Column", {"Date", "Tool", "Module", "Index", "StartDT - Copy", "EndDT - Copy"}, {{"newStartDT", each List.Min([StartDT]), type nullable datetime}, {"newEndDT", each List.Max([EndDT]), type nullable datetime}})
in
#"Grouped Rows2"

 

 

But i'm facing lot of performence issues. let assue to process 30000 rows it self  it's taking 6 hours.  Can anyone help me some better solution...

Message 4 of 11
1,522 Views
0
ImkeF
Community Champion
Community Champion

‎09-15-2023 01:37 PM

Hi @hanuraolm ,
if my answer has solved your problem, please mark it as a solution.
Thanks.

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Message 3 of 11
1,592 Views
ImkeF
Community Champion
Community Champion

‎09-15-2023 05:53 AM

Hello @hanuraolm ,
there are many ways to solve this, but if we would exactly follow your logic, then we'd need some recursive logic here that is potentially slow.
However, Power Query has a very neat optional 5th parameter in its group function that allows a more straightforward approach here:

 

// Table1
let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
  #"Changed Type" = Table.TransformColumnTypes(
    Source,
    {
      {"Date", type text},
      {"Tool", type text},
      {"Module", type text},
      {"StartDT", type text},
      {"EndDT", type text}
    }
  ),
  #"Changed Type1" = Table.TransformColumnTypes(
    #"Changed Type",
    {{"StartDT", type datetime}, {"EndDT", type datetime}},
    "en-US"
  ),
  #"Grouped Rows1" = Table.Group(
    #"Changed Type1",
    {"Tool", "Module", "EndDT"},
    {
      {"StartDT_new", each List.Min([StartDT]), type nullable datetime},
      {"EndDT_new", each List.First([EndDT]), type nullable datetime},
      {"Shift", each _}
    },
    0,
    (x, y) => Number.From(x[EndDT] < y[EndDT] or x[Module] <> y[Module] or x[Tool] <> y[Tool])
  ),
  #"Added Index" = Table.AddIndexColumn(#"Grouped Rows1", "Index", 0, 1, Int64.Type),
  #"Expanded Shift" = Table.ExpandTableColumn(
    #"Added Index",
    "Shift",
    {"Date", "StartDT"},
    {"Date", "StartDT"}
  )
in
  #"Expanded Shift"

 

If you want to understand more about this function, please check out this blogpost:
Table.Group: Exploring the 5th element in Power BI and Power Query – (thebiccountant.com)
Sample file is also attached.

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Preview file
21 KB
Message 2 of 11
1,601 Views

Helpful resources

Announcements
Power BI DataViz World Championships

Power BI Dataviz World Championships

The Power BI Data Visualization World Championships is back! Get ahead of the game and start preparing now!

November Power BI Update Carousel

Power BI Monthly Update - November 2025

Check out the November 2025 Power BI update to learn about new features.

FabCon Atlanta 2026 carousel

FabCon Atlanta 2026

Join us at FabCon Atlanta, March 16-20, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.

View All