Log In
Register
Home
Trusted Members
October 18, 2018
Offline
Using the source data in Table 3, create a listing of decimals in tenth increments that are between the values in columns named Start and End.
See Answer
October 5, 2010
Offline
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
Moderators
January 31, 2022
Offline
Just wondering why List.Numbers is needed.
You can add a column like:
List.Transform( {[Start]*10 .. [End]*10}, each _ / 10 )
Answers Post
The following users say thank you to Riny van Eekelen for this useful post:
Philip Treacy
Trusted Members
Moderators
November 1, 2018
Offline
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.
The following users say thank you to Velouria for this useful post:
Philip TreacyTrusted Members
October 18, 2018
Offline
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.
The following users say thank you to Alan Sidman for this useful post:
Philip TreacyTrusted Members
Moderators
November 1, 2018
Offline
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"
The following users say thank you to Velouria for this useful post:
Alan Sidman
1 Guest(s)
