Forum Discussion

sauliux's avatar
sauliux
Frequent Visitor
3 years ago
Solved

Grouping Times Series Data

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 ...
Need Help
  • ImkeF's avatar
    ImkeF
    3 years ago

    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.