Hello,
thanks to everyone who participated! I did expect various approaches and wasn’t disapointed. 
I’ll add three more solutions of which I think that they are on the easy side.
These workouts are very often based on real world tasks. This one too and in the real world it was just about the easier task with 10.
Let’s start off on the Add Column tab with a Custom Column:
We call it Intervals and use this simple formula to build a list:
{[SEGMENT CRITERIA1]/10…[SEGMENT CRITERIA2]/10}
The “trick” here is to divide both the interval start and the interval end by 10.
We can now get rid already of SEGMENT CRITERIA1 and EGMENT CRITERIA1 by clicking on them and pressing Delete (no picture for that).
Then just expand column Intervals to new rows.
Mark the Intervals column and on the Transform tab select Standard > Multiply
Enter 10 in the Multiply menu:
Done!
let
Source = Excel.CurrentWorkbook(){[Name="Segments"]}[Content],
#"Added Custom" = Table.AddColumn(Source, "Intervals", each {[SEGMENT CRITERIA1]/10..[SEGMENT CRITERIA2]/10}),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"SEGMENT CRITERIA1", "SEGMENT CRITERIA2"}),
#"Expanded Custom" = Table.ExpandListColumn(#"Removed Columns", "Intervals"),
#"Multiplied Column" = Table.TransformColumns(#"Expanded Custom", {{"Intervals", each _ * 10, type number}})
in
#"Multiplied Column"
This approach is UI supported, super easy and was good enough for the real world! And just change the 10 with 5 and it will provide you also intervals of 5.
Here is another UI supported solution - basically @AlejandroSimon’s solution just built with the UI.
Let’s start off again on the Add Column tab with a Custom Column:
We call it Intervals and use this simple formula - without any division - to build a list:
{[SEGMENT CRITERIA1]…[SEGMENT CRITERIA2]}
We can now get rid of SEGMENT CRITERIA1 and EGMENT CRITERIA1 by clicking on them and pressing Delete (again no picture for that).
Then just expand column Intervals to new rows.
Now right mouse on the Intervals column and select Change Type > Text:
Again on Intervals click on the top right triangle and select Text Filters > Ends With…
We want to keep rows which end with 0 Or 5
Finally, we change back the data type to Whole Number and we are done:
let
Source = Excel.CurrentWorkbook(){[Name="Segments"]}[Content],
#"Added Custom" = Table.AddColumn(Source, "Intervals", each {[SEGMENT CRITERIA1]..[SEGMENT CRITERIA2]}),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"SEGMENT CRITERIA1", "SEGMENT CRITERIA2"}),
#"Expanded Custom" = Table.ExpandListColumn(#"Removed Columns", "Intervals"),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded Custom",{{"Intervals", type text}}),
#"Filtered Rows" = Table.SelectRows(#"Changed Type", each Text.EndsWith([Intervals], "0") or Text.EndsWith([Intervals], "5")),
#"Changed Type1" = Table.TransformColumnTypes(#"Filtered Rows",{{"Intervals", Int64.Type}})
in
#"Changed Type1"
Let no one complain that it has too many steps or takes up too much memory. It’s a valid solution and still super easy!
For the final solution I won’t go step by step because there are only 3 simple steps. Add column > remove columns > expand column. The “secret” is in using List.Numbers:
List.Numbers(start as number, count as number, optional increment as nullable number)
start is obviously [SEGMENT CRITERIA1]
count is the number of values to create and needs a bit more attention, we calculate it by dividing the Intervals span plus 5 by 5
increment is of course 5
List.Numbers([SEGMENT CRITERIA1],([SEGMENT CRITERIA2]-[SEGMENT CRITERIA1]+5)/5, 5)
let
Source = Excel.CurrentWorkbook(){[Name="Segments"]}[Content],
#"Added Custom" = Table.AddColumn(Source, "Intervals", each List.Numbers([SEGMENT CRITERIA1],([SEGMENT CRITERIA2]-[SEGMENT CRITERIA1]+5)/5, 5)),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"SEGMENT CRITERIA1", "SEGMENT CRITERIA2"}),
#"Expanded Custom" = Table.ExpandListColumn(#"Removed Columns", "Intervals")
in
#"Expanded Custom"
I think this is a very good use of List.Numbers. Probably not a formula known to beginners, but if you can get here your head around count this solution is probably the easiest of the three solutions.
Try them out and make sure to check out the other approaches above! => Hope you learn something!
Big thanks to everyone who has blurred or hidden the details! It allows readers to develop their own ideas without being influenced - and then it’s easy enough to check other posts.
))
