

Trusted Members

October 18, 2018



October 5, 2010

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


Trusted Members
Moderators
Power BI

January 31, 2022



Trusted Members
Moderators

November 1, 2018



Trusted Members

October 18, 2018

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 Treacy

Trusted Members
Moderators

November 1, 2018

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 Sidman1 Guest(s)
