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: 

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
Anonymous
Not applicable

need help on how to substract rows max - min in column TerminatedDate that fullfill some criteria.

‎02-18-2020 08:50 AM

Hi everyone,

 

I am having some issues with doing this as underneath:

 

I would like to groupBY RemoteID_shaped column and keep only rows =6 in MediaType column while substracting for each RemoteID_shaped number row in column TerminatedDateTimeUTC that has column MediaType =6 from MAX(TerminatedDateTimeUTC ) rows that has column MediaType =0.

 

terminateddate.JPG

 

any help will be much appreciated.

Thank you in advance.

Gmooh.

 

 
Labels:
Message 1 of 11
1,090 Views
0
1 ACCEPTED SOLUTION
ImkeF
Community Champion
Community Champion
In response to Anonymous

‎02-24-2020 11:27 AM

Hi @Anonymous  

please paste this code into the advanced editor and follow the steps:

 

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("nZU5bsMwEEWvEqg2YM7GrXQOkAMYLpyki5E08f1DUXDD4QjjdMKHnt6I+CTP5wUDF2EBSstheb3ebqfrx1d7BDoGPGLA8AJSuQxRrCLL5TDwb/ff95/796fiBWaRl+8yyy8hh9I+IPb8qcLIp4qkecPv5g1/rhRUxOz2Nx4tngOlDAWY9vyo/USaN/2o/e2XnH4MNUQVUfb6/bzpZ1GRTOY3/RO+bYnOQ6QgGQvb6x8rRdUfjpo3+9+nU5GXT2r9tsg7fyu77j9N/t/0o/Y/+uPj0yza+CwJAkXY7X8eolJhwpv9F55FXr7LPH6zv8A6Eq9/e9ngBbhkwshxr7849q8diaR5s784rl+P/P5x/61ResI/4SU/w4/9jY/92/kEEfbXb7w/03okKP4f95/Hn6bnT3DzRfVvjfzz95c9vH1/oIpgMr99f2i+RZfLHw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [RemoteID = _t, MediaType_name = _t, ConnectedDate = _t, TerminatedDate = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"RemoteID", Int64.Type}, {"MediaType_name", type text}, {"ConnectedDate", type datetime}, {"TerminatedDate", type datetime}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"RemoteID"}, {{"CallBacks", each Table.SelectRows(_, each [MediaType_name] = "CallBack")}, {"Outbounds", each Table.SelectRows(_, each [MediaType_name] = "Outbound")}}),
    #"Expanded CallBacks" = Table.ExpandTableColumn(#"Grouped Rows", "CallBacks", {"MediaType_name", "ConnectedDate", "TerminatedDate"}, {"MediaType_name", "ConnectedDate", "TerminatedDate"}),
    DiffTerminatedDate = Table.AddColumn(#"Expanded CallBacks", "Diff-TerminatedDate", each [TerminatedDate] - List.Max([Outbounds][TerminatedDate])),
    #"Added Custom" = Table.AddColumn(DiffTerminatedDate, "Diff-ConnectedDate", each [ConnectedDate] - List.Min([Outbounds][ConnectedDate]))
in
    #"Added Custom"

 

The reason for asking for sample data is that seeing the desired result helps understanding the explanations for the problem/requirements. And prevents silly code, as one can detect small errors or typos in the description.

This was missing from what you've sent so I have not been able to check if the negative amounts are really correct here. 

 

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
960 Views
0
10 REPLIES 10
ImkeF
Community Champion
Community Champion

‎02-18-2020 01:44 PM

Hi @Anonymous 

that's possible, for sure. Please provide link to some sample data that provides the original data and the desired output.

 

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 2 of 11
1,036 Views
0
Anonymous
Not applicable
In response to ImkeF

‎02-19-2020 07:37 AM

Hi @ ,

 

I would like to GROUPBY RemoteID Column as well as add to columns:

Diff-TerminatedDate = TerminatedDate(for each row: CallBack)-MAX(TerminatedDate(of all Outbound rows for the same RemoteID)

Diff-ConnectedDate = ConnectedDate(for each row: CallBack)-Min(ConnectedDate( of all Outbound rows for the same RemoteID))

Here is my table Data sample synthesized with 2 added Columns:

RemoteIDMediaType_nameConnectedDateTerminatedDateDiff-Terminated
2046125564CallBack2020-02-13 12:23:012020-02-13 12:34:36TerminatedDate(CallBack)-MAX(TerminatedDate(Outbound)
2046125564Outbound2020-02-13 12:26:092020-02-13 12:32:36-
2049545137CallBack2020-02-13 15:49:112020-02-13 16:55:25TerminatedDate(CallBack)-MAX(TerminatedDate(Outbound)
2049545137Outbound2020-02-13 15:51:342020-02-13 15:51:37-
2049545137Outbound2020-02-13 16:55:132020-02-13 16:55:15-
2508094515CallBack2020-02-13 17:15:092020-02-13 17:23:44TerminatedDate(CallBack)-MAX(TerminatedDate(Outbound)
2508094515Outbound2020-02-13 17:15:312020-02-13 17:23:26-
2508094515CallBack2020-02-13 18:30:082020-02-13 18:44:36TerminatedDate(CallBack)-MAX(TerminatedDate(Outbound)
2508094515Outbound2020-02-13 18:32:252020-02-13 18:44:09-
4037819143CallBack2020-02-13 18:20:512020-02-13 18:33:21TerminatedDate(CallBack)-MAX(TerminatedDate(Outbound)
4037819143Outbound2020-02-13 18:22:172020-02-13 18:30:01-
4037819143CallBack2020-02-13 20:06:072020-02-13 20:38:01TerminatedDate(CallBack)-MAX(TerminatedDate(Outbound)
4037819143Outbound2020-02-13 20:06:192020-02-13 20:38:30-
4037819143CallBack2020-02-13 20:45:122020-02-13 20:50:58TerminatedDate(CallBack)-MAX(TerminatedDate(Outbound)
4037819143Outbound2020-02-13 20:45:212020-02-13 20:49:49-
4163058294CallBack2020-02-13 16:36:592020-02-13 17:46:31TerminatedDate(CallBack)-MAX(TerminatedDate(Outbound)
4163058294Outbound2020-02-13 16:43:002020-02-13 16:43:07-
4163058294Outbound2020-02-13 17:06:362020-02-13 17:06:42-
4163058294CallBack2020-02-13 17:25:492020-02-13 17:39:23TerminatedDate(CallBack)-MAX(TerminatedDate(Outbound)
4163058294Outbound2020-02-13 17:26:122020-02-13 17:30:21-
4163058294Outbound2020-02-13 17:27:152020-02-13 17:27:22-
4185710361CallBack2020-02-13 18:28:512020-02-13 19:17:59TerminatedDate(CallBack)-MAX(TerminatedDate(Outbound)
4185710361Outbound2020-02-13 18:54:522020-02-13 18:54:55-
4185710361Outbound2020-02-13 19:17:372020-02-13 19:17:52-
4185710361CallBack2020-02-13 20:14:572020-02-13 20:15:48TerminatedDate(CallBack)-MAX(TerminatedDate(Outbound)
4185710361Outbound2020-02-13 20:15:422020-02-13 20:15:53-
5149832646CallBack2020-02-13 16:23:342020-02-13 17:13:09TerminatedDate(CallBack)-MAX(TerminatedDate(Outbound)
5149832646Outbound2020-02-13 16:24:162020-02-13 16:24:43-
5149832646CallBack2020-02-13 16:45:312020-02-13 16:47:50TerminatedDate(CallBack)-MAX(TerminatedDate(Outbound)
5149832646Outbound2020-02-13 16:45:572020-02-13 16:58:45-
5149832646Outbound2020-02-13 16:46:542020-02-13 16:46:59-
5149871616CallBack2020-02-13 16:29:572020-02-13 17:36:47TerminatedDate(CallBack)-MAX(TerminatedDate(Outbound)
5149871616Outbound2020-02-13 16:55:032020-02-13 16:55:10-
5149871616CallBack2020-02-13 17:36:002020-02-13 17:40:44TerminatedDate(CallBack)-MAX(TerminatedDate(Outbound)
5149871616CallBack2020-02-13 19:14:542020-02-13 19:16:58TerminatedDate(CallBack)-MAX(TerminatedDate(Outbound)
5149871616Outbound2020-02-13 19:16:192020-02-13 19:16:48-
5149871616CallBack2020-02-13 20:02:132020-02-13 20:10:39TerminatedDate(CallBack)-MAX(TerminatedDate(Outbound)
5149871616Outbound2020-02-13 20:02:162020-02-13 20:02:35-

 

Here is the Output that I would like to have:

RemoteIDMediaType_nameConnectedDateTerminatedDateDiff-Terminated
2046125564CallBack2020-02-13 12:23:012020-02-13 12:34:36TerminatedDate(CallBack)-MAX(TerminatedDate(Outbound)
2049545137CallBack2020-02-13 15:49:112020-02-13 16:55:25TerminatedDate(CallBack)-MAX(TerminatedDate(Outbound)
2508094515CallBack2020-02-13 17:15:092020-02-13 17:23:44TerminatedDate(CallBack)-MAX(TerminatedDate(Outbound)
2508094515CallBack2020-02-13 18:30:082020-02-13 18:44:36TerminatedDate(CallBack)-MAX(TerminatedDate(Outbound)

 

 Thank you in advance.

 

Message 3 of 11
1,021 Views
0
Anonymous
Not applicable
In response to Anonymous

‎02-24-2020 11:02 AM

Hi @ ,

 

I would like to do a GROUP BY RemoteID Column and then add  2 new columns Diff-TerminatedDate and Diff-ConnectedDate as below:

Diff-TerminatedDate = TerminatedDate(for each row: CallBack)-MAX(TerminatedDate(of all Outbound rows for the same RemoteID)

Diff-ConnectedDate = ConnectedDate(for each row: CallBack)-Min(ConnectedDate( of all Outbound rows for the same RemoteID))

 

Here is my table Data sample synthesized:

RemoteIDMediaType_nameConnectedDateTerminatedDate
2049545137CallBack2020-02-13 15:49:112020-02-13 16:55:25
2049545137Outbound2020-02-13 15:51:342020-02-13 15:51:37
2049545137Outbound2020-02-13 16:55:132020-02-13 16:55:15
2508094515CallBack2020-02-13 17:15:092020-02-13 17:23:44
2508094515Outbound2020-02-13 17:15:312020-02-13 17:23:26
2508094515CallBack2020-02-13 18:30:082020-02-13 18:44:36
2508094515Outbound2020-02-13 18:32:252020-02-13 18:44:09
4037819143CallBack2020-02-13 18:20:512020-02-13 18:33:21
4037819143Outbound2020-02-13 18:22:172020-02-13 18:30:01
4037819143CallBack2020-02-13 20:06:072020-02-13 20:38:01
4037819143Outbound2020-02-13 20:06:192020-02-13 20:38:30
4037819143CallBack2020-02-13 20:45:122020-02-13 20:50:58
4037819143Outbound2020-02-13 20:45:212020-02-13 20:49:49
4163058294CallBack2020-02-13 16:36:592020-02-13 17:46:31
4163058294Outbound2020-02-13 16:43:002020-02-13 16:43:07
4163058294Outbound2020-02-13 17:06:362020-02-13 17:06:42
4163058294CallBack2020-02-13 17:25:492020-02-13 17:39:23
4163058294Outbound2020-02-13 17:26:122020-02-13 17:30:21
4163058294Outbound2020-02-13 17:27:152020-02-13 17:27:22
4185710361CallBack2020-02-13 18:28:512020-02-13 19:17:59
4185710361Outbound2020-02-13 18:54:522020-02-13 18:54:55
4185710361Outbound2020-02-13 19:17:372020-02-13 19:17:52
4185710361CallBack2020-02-13 20:14:572020-02-13 20:15:48
4185710361Outbound2020-02-13 20:15:422020-02-13 20:15:53
5149832646CallBack2020-02-13 16:23:342020-02-13 17:13:09
5149832646Outbound2020-02-13 16:24:162020-02-13 16:24:43
5149832646CallBack2020-02-13 16:45:312020-02-13 16:47:50
5149832646Outbound2020-02-13 16:45:572020-02-13 16:58:45
5149832646Outbound2020-02-13 16:46:542020-02-13 16:46:59
5149871616CallBack2020-02-13 16:29:572020-02-13 17:36:47
5149871616Outbound2020-02-13 16:55:032020-02-13 16:55:10
5149871616CallBack2020-02-13 17:36:002020-02-13 17:40:44
5149871616CallBack2020-02-13 19:14:542020-02-13 19:16:58
5149871616Outbound2020-02-13 19:16:192020-02-13 19:16:48
5149871616CallBack2020-02-13 20:02:132020-02-13 20:10:39
5149871616Outbound2020-02-13 20:02:162020-02-13 20:02:35

 

The output will be as below:

RemoteIDMediaType_nameConnectedDateTerminatedDateDiff-TerminatedDateDiff-ConnectedDate
2049545137CallBack2020-02-13 15:49:112020-02-13 16:55:25  
2508094515CallBack2020-02-13 17:15:092020-02-13 17:23:44  
2508094515CallBack2020-02-13 18:30:082020-02-13 18:44:36  
4037819143CallBack2020-02-13 18:20:512020-02-13 18:33:21  
4037819143CallBack2020-02-13 20:06:072020-02-13 20:38:01  
4037819143CallBack2020-02-13 20:45:122020-02-13 20:50:58  
4163058294CallBack2020-02-13 16:36:592020-02-13 17:46:31  
4163058294CallBack2020-02-13 17:25:492020-02-13 17:39:23  
4185710361CallBack2020-02-13 18:28:512020-02-13 19:17:59  
4185710361CallBack2020-02-13 20:14:572020-02-13 20:15:48  
5149832646CallBack2020-02-13 16:23:342020-02-13 17:13:09  
5149832646CallBack2020-02-13 16:45:312020-02-13 16:47:50  
5149871616CallBack2020-02-13 16:29:572020-02-13 17:36:47  
5149871616CallBack2020-02-13 17:36:002020-02-13 17:40:44  
5149871616CallBack2020-02-13 19:14:542020-02-13 19:16:58  
5149871616CallBack2020-02-13 20:02:132020-02-13 20:10:39  

Thank you in advance.

Message 8 of 11
952 Views
0
ImkeF
Community Champion
Community Champion
In response to Anonymous

‎02-24-2020 11:27 AM

Hi @Anonymous  

please paste this code into the advanced editor and follow the steps:

 

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("nZU5bsMwEEWvEqg2YM7GrXQOkAMYLpyki5E08f1DUXDD4QjjdMKHnt6I+CTP5wUDF2EBSstheb3ebqfrx1d7BDoGPGLA8AJSuQxRrCLL5TDwb/ff95/796fiBWaRl+8yyy8hh9I+IPb8qcLIp4qkecPv5g1/rhRUxOz2Nx4tngOlDAWY9vyo/USaN/2o/e2XnH4MNUQVUfb6/bzpZ1GRTOY3/RO+bYnOQ6QgGQvb6x8rRdUfjpo3+9+nU5GXT2r9tsg7fyu77j9N/t/0o/Y/+uPj0yza+CwJAkXY7X8eolJhwpv9F55FXr7LPH6zv8A6Eq9/e9ngBbhkwshxr7849q8diaR5s784rl+P/P5x/61ResI/4SU/w4/9jY/92/kEEfbXb7w/03okKP4f95/Hn6bnT3DzRfVvjfzz95c9vH1/oIpgMr99f2i+RZfLHw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [RemoteID = _t, MediaType_name = _t, ConnectedDate = _t, TerminatedDate = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"RemoteID", Int64.Type}, {"MediaType_name", type text}, {"ConnectedDate", type datetime}, {"TerminatedDate", type datetime}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"RemoteID"}, {{"CallBacks", each Table.SelectRows(_, each [MediaType_name] = "CallBack")}, {"Outbounds", each Table.SelectRows(_, each [MediaType_name] = "Outbound")}}),
    #"Expanded CallBacks" = Table.ExpandTableColumn(#"Grouped Rows", "CallBacks", {"MediaType_name", "ConnectedDate", "TerminatedDate"}, {"MediaType_name", "ConnectedDate", "TerminatedDate"}),
    DiffTerminatedDate = Table.AddColumn(#"Expanded CallBacks", "Diff-TerminatedDate", each [TerminatedDate] - List.Max([Outbounds][TerminatedDate])),
    #"Added Custom" = Table.AddColumn(DiffTerminatedDate, "Diff-ConnectedDate", each [ConnectedDate] - List.Min([Outbounds][ConnectedDate]))
in
    #"Added Custom"

 

The reason for asking for sample data is that seeing the desired result helps understanding the explanations for the problem/requirements. And prevents silly code, as one can detect small errors or typos in the description.

This was missing from what you've sent so I have not been able to check if the negative amounts are really correct here. 

 

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
961 Views
0
Anonymous
Not applicable
In response to ImkeF

‎02-25-2020 07:35 AM

Hi @ImkeF ,

 

It matches perfectly my need.

 

thanks again.

Message 11 of 11
740 Views
0
Anonymous
Not applicable
In response to ImkeF

‎02-24-2020 11:33 AM

Hi @ImkeF ,

 

Thank you for your reply, please forgive my bad explanantion :). 

I will check that and try  to implement it to my code.I will let you know ASAP.

 

Thanks.

Message 10 of 11
947 Views
0
Anonymous
Not applicable
In response to Anonymous

‎02-24-2020 09:38 AM

Hi everyone,

 

Could anyone help on this please ?

 

Much appreciated.

 

Thanks.

 

 

Message 4 of 11
970 Views
0
ImkeF
Community Champion
Community Champion
In response to Anonymous

‎02-24-2020 10:31 AM

Hi @Anonymous 

Sorry, misses your answer.

Will check it out in a sec.

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 5 of 11
960 Views
0
ImkeF
Community Champion
Community Champion
In response to ImkeF

‎02-24-2020 10:41 AM

 Hi @Anonymous 

could you please check the data for the desired output?

I cannot see any calculation there. So its just a selection of the first 4 items with "MediaType" = "CallBack"

 

Please make sure that the desired input and output match each other completely.

 

 

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
957 Views
0
Anonymous
Not applicable
In response to ImkeF

‎02-24-2020 10:37 AM

Thanks ImkeF, much appreciated.

Message 6 of 11
958 Views
0

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

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

View All
Top Solution Authors
View All