Hi Alan,

See attached file.

When I was doing this I was getting a problem (bug?) with List.Numbers.  The idea is to create a list of x numbers where x is ([Start] - [End]) * 10 + 1.

The code is List.Numbers([Start], ([End]-[Start])*10+1, 0.1)

So for ID 100 you should get (4.2 - 3.8) * 10 + 1 = 5 numbers in the sequence, but List.Numbers was giving me 6.

For some of the ID's List.Numbers gave the right sequence, for others it did not.

I fixed this by creating a new column to hold the ([Start] - [End]) * 10 + 1 value and explicitly changing the column to Whole Number.

You shouldn't need to do that but it fixed the problem.

There are 2 queries in the file, the one that works and the one with the issue.  I left that one there for you to check out if you are curious.

Regards

Phil

Just wondering why List.Numbers is needed.

You can add a column like:
List.Transform( {[Start]*10 .. [End]*10}, each _ / 10 )

I'd guess it's a floating point issue. You could also use Number.Round(([End]-[Start])*10+1,0) directly within the List.Numbers function.

Thanks all.  Philip, understand what happened there and tried to get Rory's suggestion on the floating point to work but could not get the syntax to not error out for me.  Rory, if you have a moment, could you expand the Mcode to show the complete statement using Philip's line of code as I couldn't not get it to work for me.  I have marked Riny's suggestion as having solved the issue.  Thanks again.

Hi Alan,

It would be:

let
Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Start", type number}, {"End", type number}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each List.Numbers([Start], Number.Round(([End]-[Start])*10+1,0), 0.1)),
#"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
#"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"Start", "End"})
in
#"Removed Columns"

Rory,

Thanks for the code.  I got it now.  Works Perfectly.

Alan