Solved: Grouping Times Series Data

sauliux · ‎10-26-2022

In short I have manually recorded luxmeter data with values of interest, and 0 values inbetween (in order to keep track of which measurements are for which sample).
I want to group this time series data highs in a serial fashion, and I am struggling to figure out how to write the conditional statements for the "Custom Column" (I suspect the magical "List.Generate" and "Custom Function" have something to do with it).
So far I was able to "find" the data boundaries of interest ("FALSE" values in the "Data Boundries" column from the image below), but serial numbering the time series data highs is turning out to be a challenge:

I was hoping that declaring a variable (in this case "n"), and then in an if conditional statement adding one to it (n=n+1) would allow me to number my groups. Apparently the variable is immutable?
For the current stage I am hoping to achieve the following:

At a later stage I would filter out the 0 values from the "Custom" column, group the data by "Data Boundries" column, average the the 5 "middle" values in the grouped "Data Boundries" tables (last and first 2-3 measurements of each group contain discrepancies)
Suggestions how incorporate n=n+1 into the custom column function would be highly appreciated.

ImkeF · ‎10-27-2022

Hi @sauliux ,
you can do this with special grouping parameters: (If you are interested: Table.Group: Exploring the 5th element in Power BI and Power Query – The BIccountant )

let
  Source = = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], 
  #"Changed Type" = Table.TransformColumnTypes(
    Source, 
    {
      {"Record Time", type text}, 
      {"Solar", Int64.Type}, 
      {"Index", Int64.Type}, 
      {"Custom", Int64.Type}, 
      {"Custom1", Int64.Type}, 
      {"Data_Boundries", type any}
    }
  ), 
  #"Grouped Rows" = Table.Group(
    #"Changed Type", 
    {"Data_Boundries"}, 
    {{"Count", each _}}, 
    GroupKind.Local, 
    (group, current) => Number.From(current[Data_Boundries] = false)
  ), 
  #"Added Index" = Table.AddIndexColumn(#"Grouped Rows", "Index", 1, 1, Int64.Type), 
  #"Expanded Count" = Table.ExpandTableColumn(
    #"Added Index", 
    "Count", 
    {"Record Time", "Solar", "Index", "Custom", "Custom1", "Data_Boundries"}, 
    {"Record Time", "Solar", "Index.1", "Custom", "Custom1", "Data_Boundries.1"}
  ), 
  #"Added Custom" = Table.AddColumn(
    #"Expanded Count", 
    "Custom.1", 
    each if [Data_Boundries.1] = 0 then 0 else [Index]
  ), 
  #"Removed Columns" = Table.RemoveColumns(
    #"Added Custom", 
    {"Data_Boundries", "Data_Boundries.1", "Index"}
  ), 
  #"Renamed Columns" = Table.RenameColumns(#"Removed Columns", {{"Custom.1", "Data_Boundries"}})
in
  #"Renamed Columns"

See also attached file.

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

ImkeF · ‎10-27-2022

Hi @sauliux ,
you can do this with special grouping parameters: (If you are interested: Table.Group: Exploring the 5th element in Power BI and Power Query – The BIccountant )

let
  Source = = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], 
  #"Changed Type" = Table.TransformColumnTypes(
    Source, 
    {
      {"Record Time", type text}, 
      {"Solar", Int64.Type}, 
      {"Index", Int64.Type}, 
      {"Custom", Int64.Type}, 
      {"Custom1", Int64.Type}, 
      {"Data_Boundries", type any}
    }
  ), 
  #"Grouped Rows" = Table.Group(
    #"Changed Type", 
    {"Data_Boundries"}, 
    {{"Count", each _}}, 
    GroupKind.Local, 
    (group, current) => Number.From(current[Data_Boundries] = false)
  ), 
  #"Added Index" = Table.AddIndexColumn(#"Grouped Rows", "Index", 1, 1, Int64.Type), 
  #"Expanded Count" = Table.ExpandTableColumn(
    #"Added Index", 
    "Count", 
    {"Record Time", "Solar", "Index", "Custom", "Custom1", "Data_Boundries"}, 
    {"Record Time", "Solar", "Index.1", "Custom", "Custom1", "Data_Boundries.1"}
  ), 
  #"Added Custom" = Table.AddColumn(
    #"Expanded Count", 
    "Custom.1", 
    each if [Data_Boundries.1] = 0 then 0 else [Index]
  ), 
  #"Removed Columns" = Table.RemoveColumns(
    #"Added Custom", 
    {"Data_Boundries", "Data_Boundries.1", "Index"}
  ), 
  #"Renamed Columns" = Table.RenameColumns(#"Removed Columns", {{"Custom.1", "Data_Boundries"}})
in
  #"Renamed Columns"

See also attached file.

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

sauliux · ‎10-28-2022

@ImkeF That was so smooth. Thank you very much.
I suspect that a lot of the M code List.Accumulate() and/or List.Generate() loop examples that I have come across could have been done using your Table.Group() technique.

ImkeF · ‎10-26-2022

Hi @sauliux ,
having trouble understanding your request.
Could you please proved sample data with a "before" and "after" example in a usable format like described here?:

https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...

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

sauliux · ‎10-27-2022

Sorry, but I have tried everything, including spreadsheet to HTML table generators, and cannot reproduce what you have requested in "in a usable format like described here".
Look, even if I generate the HTML table with with forum toolbar and fill it manualy it still throws HTML errors...

sauliux · ‎10-27-2022

I have created a ""before" sample data" .csv file, uploaded it to Onedrive, and it's available via the following URL: https://1drv.ms/u/s!Avj2FfL1S_6RnBSVJ886R9rVtp9N?e=NLGnCd

sauliux · ‎10-27-2022

"before" sample data

Record Time	Solar	Index	Custom	Custom1	Data_Boundries
18-10-2022 14:01:01	279	1	1	1	1
18-10-2022 14:01:02	402,8	2	1	0	1
18-10-2022 14:01:03	0	3	0	0	0
18-10-2022 14:01:04	0	4	0	0	0
18-10-2022 14:01:05	0	5	0	0	0
18-10-2022 14:01:06	0	6	0	0	0
18-10-2022 14:01:07	0	7	0	0	0
18-10-2022 14:01:08	0	8	0	0	0
18-10-2022 14:01:09	0	9	0	0	0
18-10-2022 14:01:10	0	10	0	0	0
18-10-2022 14:01:11	0	11	0	1	FALSE
18-10-2022 14:01:12	513,7	12	1	1	1
18-10-2022 14:01:13	473,7	13	1	1	1
18-10-2022 14:01:14	473,3	14	1	1	1
18-10-2022 14:01:15	482,2	15	1	1	1
18-10-2022 14:01:16	480,5	16	1	1	1
18-10-2022 14:01:17	480,5	17	1	1	1
18-10-2022 14:01:18	481,6	18	1	1	1
18-10-2022 14:01:19	480,5	19	1	1	1
18-10-2022 14:01:20	478,8	20	1	1	1
18-10-2022 14:01:21	485,7	21	1	0	1
18-10-2022 14:01:22	0	22	0	0	0
18-10-2022 14:01:23	0	23	0	0	0
18-10-2022 14:01:24	0	24	0	0	0
18-10-2022 14:01:25	0	25	0	0	0
18-10-2022 14:01:26	0	26	0	0	0
18-10-2022 14:01:27	0	27	0	0	0
18-10-2022 14:01:28	0	28	0	0	0
18-10-2022 14:01:29	0	29	0	0	0
18-10-2022 14:01:30	0	30	0	0	0
18-10-2022 14:01:31	0	31	0	1	FALSE
18-10-2022 14:01:32	291,8	32	1	1	1
18-10-2022 14:01:33	321	33	1	1	1
18-10-2022 14:01:34	321,6	34	1	1	1
18-10-2022 14:01:35	321,6	35	1	1	1
18-10-2022 14:01:36	321,3	36	1	1	1
18-10-2022 14:01:37	321,3	37	1	1	1
18-10-2022 14:01:38	321,3	38	1	1	1
18-10-2022 14:01:39	321	39	1	1	1
18-10-2022 14:01:40	321,3	40	1	1	1
18-10-2022 14:01:41	321,3	41	1	1	1
18-10-2022 14:01:42	321,6	42	1	0	1
18-10-2022 14:01:43	0	43	0	0	0
18-10-2022 14:01:44	0	44	0	0	0
18-10-2022 14:01:45	0	45	0	0	0
18-10-2022 14:01:46	0	46	0	0	0
18-10-2022 14:01:47	0	47	0	0	0

sauliux · ‎10-27-2022

"after" sample data

Record Time	Solar	Index	Custom	Custom.1	Data Boundries
18-10-2022 14:01:01	279	1	1	1	1
18-10-2022 14:01:02	402,8	2	1	0	1
18-10-2022 14:01:03	0	3	0	0	0
18-10-2022 14:01:04	0	4	0	0	0
18-10-2022 14:01:05	0	5	0	0	0
18-10-2022 14:01:06	0	6	0	0	0
18-10-2022 14:01:07	0	7	0	0	0
18-10-2022 14:01:08	0	8	0	0	0
18-10-2022 14:01:09	0	9	0	0	0
18-10-2022 14:01:10	0	10	0	0	0
18-10-2022 14:01:11	0	11	0	1	2
18-10-2022 14:01:12	513,7	12	1	1	2
18-10-2022 14:01:13	473,7	13	1	1	2
18-10-2022 14:01:14	473,3	14	1	1	2
18-10-2022 14:01:15	482,2	15	1	1	2
18-10-2022 14:01:16	480,5	16	1	1	2
18-10-2022 14:01:17	480,5	17	1	1	2
18-10-2022 14:01:18	481,6	18	1	1	2
18-10-2022 14:01:19	480,5	19	1	1	2
18-10-2022 14:01:20	478,8	20	1	1	2
18-10-2022 14:01:21	485,7	21	1	0	2
18-10-2022 14:01:22	0	22	0	0	0
18-10-2022 14:01:23	0	23	0	0	0
18-10-2022 14:01:24	0	24	0	0	0
18-10-2022 14:01:25	0	25	0	0	0
18-10-2022 14:01:26	0	26	0	0	0
18-10-2022 14:01:27	0	27	0	0	0
18-10-2022 14:01:28	0	28	0	0	0
18-10-2022 14:01:29	0	29	0	0	0
18-10-2022 14:01:30	0	30	0	0	0
18-10-2022 14:01:31	0	31	0	1	3
18-10-2022 14:01:32	291,8	32	1	1	3
18-10-2022 14:01:33	321	33	1	1	3
18-10-2022 14:01:34	321,6	34	1	1	3
18-10-2022 14:01:35	321,6	35	1	1	3
18-10-2022 14:01:36	321,3	36	1	1	3
18-10-2022 14:01:37	321,3	37	1	1	3
18-10-2022 14:01:38	321,3	38	1	1	3
18-10-2022 14:01:39	321	39	1	1	3
18-10-2022 14:01:40	321,3	40	1	1	3
18-10-2022 14:01:41	321,3	41	1	1	3
18-10-2022 14:01:42	321,6	42	1	0	3
18-10-2022 14:01:43	0	43	0	0	0
18-10-2022 14:01:44	0	44	0	0	0
18-10-2022 14:01:45	0	45	0	0	0
18-10-2022 14:01:46	0	46	0	0	0
18-10-2022 14:01:47	0	47	0	0	0