CODEWITHSUNDEEP
powerbipowerquery
Gil Wadsworth
Gil Wadsworth

Reputation: 5

Power BI How to work out interval between multiple values for multiple different groups

I've got a large dataset of multiple different locations each with different readings from that location. I've been able to group the readings into a summarised table in the query editor with min and max readings to work out the length for each location. Next I'm trying to work out the average interval between each of the readings. I initially tried to do this by adding in two index columns, merging the table with itself then working out the difference between readings. This has led to some incorrect results. The issue is that the counts need to be filtered per the location name, so that I only work out the interval between values at the same location. It seems like I need some kind of iterative function to do this, but this is a bit of a jump in my power bi skills.

Any help would be greatly appreciated.

here's an example table

Table of Locations and Mile values

Upvotes: 0

Views: 753

Answers (1)

horseyride
horseyride

Reputation: 21318

In powerquery, try

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Structure Name", type text}, {"Miles", type number}}),
 // offset all columns one row
      MinusOne = Table.RemoveFirstN(Table.DemoteHeaders(#"Changed Type"),2),
     custom1 = Table.ToColumns(#"Changed Type") & Table.ToColumns(MinusOne ),
     custom2 = Table.FromColumns(custom1,Table.ColumnNames(#"Changed Type")&List.Transform(Table.ColumnNames(#"Changed Type"), each _&"_offset")),
#"Added Custom" = Table.AddColumn(custom2, "Custom", each [Structure Name]=[Structure Name_offset]),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Diff", each [Miles]-[Miles_offset]),
#"Grouped Rows" = Table.Group(#"Added Custom1", {"Structure Name"}, {
  {"Min", each List.Min([Miles]), type nullable number}, 
  {"Max", each List.Max([Miles]), type nullable number},
  {"Average", each List.Average([Miles]), type nullable number},
  {"Average Interval", each  List.Average(Table.SelectRows(_, each [Custom] = true)[Diff]), type nullable number }})
in #"Grouped Rows"

enter image description here

Structure Name Miles
a 5.58606451311391
a 1.70909166045198
a 8.21338467615703
a 9.35706618413945
a 9.14297274146315
a 0.876906315131277
a 8.03868077615249
b 8.96200862989947
b 2.2390707691068
b 6.96034655870365
b 2.37255397354521
b 6.99309500530682
b 9.26615828442822
c 6.3303796585834
c 6.13382694665522
c 2.9815985137518
c 9.54206770630277
c 8.10710813048523
c 7.56372217917176
c 7.26086714404075

Upvotes: 1

Related Questions