Page 1 of 2 12 LastLast
Results 1 to 10 of 14

Thread: In Power Query - Decimals getting removed

  1. #1
    New Member
    Join Date
    Jan 2011
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default In Power Query - Decimals getting removed

    Hi All

    I've been playing with the data below. I'm trying to unpivot the age ranges in power query.

    When I do this and then try to load it to the data model all the decimals get removed. Instead of decimals I just get zeros.

    What am I doing wrong?

    Many thanks

    Miles


    User Type Severity 0-4 Yrs 5-7 Yrs 8-11 Yrs 12-15 Yrs 16 Yrs 17 Yrs 18-19 Yrs 20-24 Yrs 25-29 Yrs 30-39 Yrs 40-49 Yrs 50-59 Yrs 60-69 Yrs 70-79 Yrs 80 + Yrs Unknown
    Pedestrians Killed 5.00 3.00 6.00 12.00 4.00 5.00 14.00 33.00 29.00 44.00 49.00 48.00 31.00 47.00 68.00 0.00
    Pedestrians KSI 227.76 286.25 517.00 610.25 121.96 107.56 237.17 500.14 438.78 653.73 675.00 579.03 503.17 471.39 475.23 83.10
    Pedal cyclists Killed 0.00 1.00 1.00 4.00 1.00 2.00 3.00 10.00 5.00 15.00 18.00 24.00 14.00 8.00 3.00 0.00
    Pedal cyclists KSI 4.47 34.32 95.59 230.20 65.08 68.21 101.05 354.78 400.25 779.62 854.73 554.06 227.60 109.08 32.85 64.14
    Motorcycle 50cc and under Killed 0.00 0.00 0.00 0.00 1.00 1.00 0.00 0.00 0.00 1.00 0.00 0.00 0.00 0.00 0.00 0.00
    Motorcycle 50cc and under KSI 0.00 0.02 0.16 7.93 148.10 90.56 62.80 72.51 35.68 47.89 39.45 24.15 10.74 3.45 2.22 4.68
    Motorcycle 50cc - riders Killed 0.00 0.00 0.00 1.00 0.00 3.00 15.00 52.00 36.00 45.00 90.00 52.00 16.00 6.00 0.00 0.00
    Motorcycle 50cc - riders KSI 0.00 0.00 0.00 12.96 23.83 202.72 440.61 919.68 559.13 847.10 1108.99 800.87 305.70 52.37 9.45 41.28
    Motorcycle 50cc - Passengers Killed 0.00 0.00 1.00 0.00 0.00 0.00 2.00 1.00 1.00 1.00 5.00 0.00 1.00 0.00 0.00 0.00
    Motorcycle 50cc - Passengers KSI 0.00 0.10 3.98 4.98 6.31 7.78 25.63 38.25 27.15 24.90 50.50 27.01 19.60 2.34 0.18 4.95
    Car - Drivers Killed 0.00 0.00 0.00 0.00 0.00 1.00 44.00 86.00 70.00 62.00 60.00 59.00 49.00 58.00 59.00 0.00
    Car - Drivers KSI 0.00 0.00 0.02 1.24 5.43 110.14 449.32 1108.02 864.49 1116.12 1121.97 934.30 665.84 534.73 434.12 43.40
    Car - Passengers Killed 5.00 2.00 2.00 4.00 11.00 7.00 27.00 30.00 20.00 27.00 18.00 12.00 10.00 28.00 34.00 0.00
    Car - Passengers KSI 107.98 65.75 96.99 134.07 79.96 156.60 331.74 577.16 324.27 364.81 271.94 240.23 265.49 261.80 210.47 45.79
    Bus & coach Drivers Killed 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 1.00 1.00 0.00 0.00 0.00
    Bus & coach Drivers KSI 0.00 0.00 0.00 0.00 0.00 0.01 0.01 0.13 2.42 5.95 7.43 7.78 5.07 0.06 0.05 0.01
    Bus & coach Passengers Killed 0.00 0.00 0.00 0.00 0.00 0.00 1.00 0.00 0.00 0.00 1.00 0.00 1.00 1.00 4.00 0.00
    Bus & coach Passengers KSI 5.06 1.62 6.14 11.53 1.28 2.97 5.23 16.51 15.57 30.65 24.33 41.04 58.44 82.59 75.09 13.72
    Goods vehicle Drivers Killed 0.00 0.00 0.00 0.00 0.00 0.00 1.00 8.00 2.00 6.00 9.00 12.00 6.00 2.00 0.00 0.00
    Goods vehicle Drivers KSI 0.00 0.00 0.00 0.04 0.11 0.29 4.80 53.12 49.23 112.80 140.35 139.42 50.69 11.99 2.57 3.29
    Goods vehicle Passengers Killed 0.00 0.00 0.00 1.00 0.00 1.00 2.00 4.00 1.00 1.00 2.00 0.00 0.00 0.00 0.00 0.00
    Goods vehicle Passengers KSI 1.36 1.54 1.22 2.91 1.17 1.40 7.86 30.82 13.28 23.42 26.44 14.86 3.67 1.37 0.35 4.47
    Other vehicle/Unknown vehicle occupants Killed 0.00 0.00 0.00 0.00 0.00 0.00 1.00 1.00 1.00 0.00 2.00 5.00 2.00 5.00 5.00 0.00
    Other vehicle/Unknown vehicle occupants KSI 1.14 0.03 1.88 5.71 1.19 6.36 15.07 15.29 22.05 37.54 38.01 30.28 26.78 25.25 32.10 2.39
    All road users Killed 10.00 6.00 10.00 22.00 17.00 20.00 110.00 225.00 165.00 202.00 254.00 213.00 131.00 155.00 173.00 0.00
    All road users KSI 347.76 389.63 722.99 1021.82 454.42 754.60 1681.29 3686.42 2752.31 4044.53 4359.14 3393.01 2142.78 1556.42 1274.67 311.22

  2. #2
    Board Regular sandy666's Avatar
    Join Date
    Oct 2015
    Posts
    3,027
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    1 Thread(s)

    Cool Re: In Power Query - Decimals getting removed

    User Type Severity Attribute Value
    Pedestrians Killed 0-4 Yrs
    5
    Pedestrians Killed 5-7 Yrs
    3
    Pedestrians Killed 8-11 Yrs
    6
    Pedestrians Killed 12-15 Yrs
    12
    Pedestrians Killed 16 Yrs
    4
    Pedestrians Killed 17 Yrs
    5
    Pedestrians Killed 18-19 Yrs
    14
    Pedestrians Killed 20-24 Yrs
    33
    Pedestrians Killed 25-29 Yrs
    29
    Pedestrians Killed 30-39 Yrs
    44
    Pedestrians Killed 40-49 Yrs
    49
    Pedestrians Killed 50-59 Yrs
    48
    Pedestrians Killed 60-69 Yrs
    31
    Pedestrians Killed 70-79 Yrs
    47
    Pedestrians Killed 80 + Yrs
    68
    Pedestrians Killed Unknown
    0
    Pedestrians KSI 0-4 Yrs
    227.76
    Pedestrians KSI 5-7 Yrs
    286.25
    Pedestrians KSI 8-11 Yrs
    517
    Pedestrians KSI 12-15 Yrs
    610.25
    Pedestrians KSI 16 Yrs
    121.96
    Pedestrians KSI 17 Yrs
    107.56
    Pedestrians KSI 18-19 Yrs
    237.17
    Pedestrians KSI 20-24 Yrs
    500.14
    Pedestrians KSI 25-29 Yrs
    438.78
    Pedestrians KSI 30-39 Yrs
    653.73
    Pedestrians KSI 40-49 Yrs
    675
    Pedestrians KSI 50-59 Yrs
    579.03
    Pedestrians KSI 60-69 Yrs
    503.17
    Pedestrians KSI 70-79 Yrs
    471.39
    Pedestrians KSI 80 + Yrs
    475.23
    Pedestrians KSI Unknown
    83.1
    Pedal cyclists Killed 0-4 Yrs
    0
    Pedal cyclists Killed 5-7 Yrs
    1
    Pedal cyclists Killed 8-11 Yrs
    1
    Pedal cyclists Killed 12-15 Yrs
    4
    Pedal cyclists Killed 16 Yrs
    1
    Pedal cyclists Killed 17 Yrs
    2
    Pedal cyclists Killed 18-19 Yrs
    3
    Pedal cyclists Killed 20-24 Yrs
    10
    Pedal cyclists Killed 25-29 Yrs
    5
    Pedal cyclists Killed 30-39 Yrs
    15
    Pedal cyclists Killed 40-49 Yrs
    18
    Pedal cyclists Killed 50-59 Yrs
    24
    Pedal cyclists Killed 60-69 Yrs
    14
    Pedal cyclists Killed 70-79 Yrs
    8
    Pedal cyclists Killed 80 + Yrs
    3
    Pedal cyclists Killed Unknown
    0
    Pedal cyclists KSI 0-4 Yrs
    4.47
    Pedal cyclists KSI 5-7 Yrs
    34.32
    Pedal cyclists KSI 8-11 Yrs
    95.59
    Pedal cyclists KSI 12-15 Yrs
    230.2
    Pedal cyclists KSI 16 Yrs
    65.08
    Pedal cyclists KSI 17 Yrs
    68.21
    Pedal cyclists KSI 18-19 Yrs
    101.05
    Pedal cyclists KSI 20-24 Yrs
    354.78
    Pedal cyclists KSI 25-29 Yrs
    400.25
    Pedal cyclists KSI 30-39 Yrs
    779.62
    Pedal cyclists KSI 40-49 Yrs
    854.73
    Pedal cyclists KSI 50-59 Yrs
    554.06
    Pedal cyclists KSI 60-69 Yrs
    227.6
    Pedal cyclists KSI 70-79 Yrs
    109.08
    Pedal cyclists KSI 80 + Yrs
    32.85
    Pedal cyclists KSI Unknown
    64.14
    Motorcycle 50cc and under Killed 0-4 Yrs
    0
    Motorcycle 50cc and under Killed 5-7 Yrs
    0
    Motorcycle 50cc and under Killed 8-11 Yrs
    0
    Motorcycle 50cc and under Killed 12-15 Yrs
    0
    Motorcycle 50cc and under Killed 16 Yrs
    1
    Motorcycle 50cc and under Killed 17 Yrs
    1
    Motorcycle 50cc and under Killed 18-19 Yrs
    0
    Motorcycle 50cc and under Killed 20-24 Yrs
    0
    Motorcycle 50cc and under Killed 25-29 Yrs
    0
    Motorcycle 50cc and under Killed 30-39 Yrs
    1
    Motorcycle 50cc and under Killed 40-49 Yrs
    0
    Motorcycle 50cc and under Killed 50-59 Yrs
    0
    Motorcycle 50cc and under Killed 60-69 Yrs
    0
    Motorcycle 50cc and under Killed 70-79 Yrs
    0
    Motorcycle 50cc and under Killed 80 + Yrs
    0
    Motorcycle 50cc and under Killed Unknown
    0
    Motorcycle 50cc and under KSI 0-4 Yrs
    0
    Motorcycle 50cc and under KSI 5-7 Yrs
    0.02
    Motorcycle 50cc and under KSI 8-11 Yrs
    0.16
    Motorcycle 50cc and under KSI 12-15 Yrs
    7.93
    Motorcycle 50cc and under KSI 16 Yrs
    148.1
    Motorcycle 50cc and under KSI 17 Yrs
    90.56
    Motorcycle 50cc and under KSI 18-19 Yrs
    62.8
    Motorcycle 50cc and under KSI 20-24 Yrs
    72.51
    Motorcycle 50cc and under KSI 25-29 Yrs
    35.68
    Motorcycle 50cc and under KSI 30-39 Yrs
    47.89
    Motorcycle 50cc and under KSI 40-49 Yrs
    39.45
    Motorcycle 50cc and under KSI 50-59 Yrs
    24.15
    Motorcycle 50cc and under KSI 60-69 Yrs
    10.74
    Motorcycle 50cc and under KSI 70-79 Yrs
    3.45
    Motorcycle 50cc and under KSI 80 + Yrs
    2.22
    Motorcycle 50cc and under KSI Unknown
    4.68
    Motorcycle 50cc - riders Killed 0-4 Yrs
    0
    Motorcycle 50cc - riders Killed 5-7 Yrs
    0
    Motorcycle 50cc - riders Killed 8-11 Yrs
    0
    Motorcycle 50cc - riders Killed 12-15 Yrs
    1
    Motorcycle 50cc - riders Killed 16 Yrs
    0
    Motorcycle 50cc - riders Killed 17 Yrs
    3
    Motorcycle 50cc - riders Killed 18-19 Yrs
    15
    Motorcycle 50cc - riders Killed 20-24 Yrs
    52
    Motorcycle 50cc - riders Killed 25-29 Yrs
    36
    Motorcycle 50cc - riders Killed 30-39 Yrs
    45
    Motorcycle 50cc - riders Killed 40-49 Yrs
    90
    Motorcycle 50cc - riders Killed 50-59 Yrs
    52
    Motorcycle 50cc - riders Killed 60-69 Yrs
    16
    Motorcycle 50cc - riders Killed 70-79 Yrs
    6
    Motorcycle 50cc - riders Killed 80 + Yrs
    0
    Motorcycle 50cc - riders Killed Unknown
    0
    Motorcycle 50cc - riders KSI 0-4 Yrs
    0
    Motorcycle 50cc - riders KSI 5-7 Yrs
    0
    Motorcycle 50cc - riders KSI 8-11 Yrs
    0
    Motorcycle 50cc - riders KSI 12-15 Yrs
    12.96
    Motorcycle 50cc - riders KSI 16 Yrs
    23.83
    Motorcycle 50cc - riders KSI 17 Yrs
    202.72
    Motorcycle 50cc - riders KSI 18-19 Yrs
    440.61
    Motorcycle 50cc - riders KSI 20-24 Yrs
    919.68
    Motorcycle 50cc - riders KSI 25-29 Yrs
    559.13
    Motorcycle 50cc - riders KSI 30-39 Yrs
    847.1
    Motorcycle 50cc - riders KSI 40-49 Yrs
    1108.99
    Motorcycle 50cc - riders KSI 50-59 Yrs
    800.87
    Motorcycle 50cc - riders KSI 60-69 Yrs
    305.7
    Motorcycle 50cc - riders KSI 70-79 Yrs
    52.37
    Motorcycle 50cc - riders KSI 80 + Yrs
    9.45
    Motorcycle 50cc - riders KSI Unknown
    41.28
    Motorcycle 50cc - Passengers Killed 0-4 Yrs
    0
    Motorcycle 50cc - Passengers Killed 5-7 Yrs
    0
    Motorcycle 50cc - Passengers Killed 8-11 Yrs
    1
    Motorcycle 50cc - Passengers Killed 12-15 Yrs
    0
    Motorcycle 50cc - Passengers Killed 16 Yrs
    0
    Motorcycle 50cc - Passengers Killed 17 Yrs
    0
    Motorcycle 50cc - Passengers Killed 18-19 Yrs
    2
    Motorcycle 50cc - Passengers Killed 20-24 Yrs
    1
    Motorcycle 50cc - Passengers Killed 25-29 Yrs
    1
    Motorcycle 50cc - Passengers Killed 30-39 Yrs
    1
    Motorcycle 50cc - Passengers Killed 40-49 Yrs
    5
    Motorcycle 50cc - Passengers Killed 50-59 Yrs
    0
    Motorcycle 50cc - Passengers Killed 60-69 Yrs
    1
    Motorcycle 50cc - Passengers Killed 70-79 Yrs
    0
    Motorcycle 50cc - Passengers Killed 80 + Yrs
    0
    Motorcycle 50cc - Passengers Killed Unknown
    0
    Motorcycle 50cc - Passengers KSI 0-4 Yrs
    0
    Motorcycle 50cc - Passengers KSI 5-7 Yrs
    0.1
    Motorcycle 50cc - Passengers KSI 8-11 Yrs
    3.98
    Motorcycle 50cc - Passengers KSI 12-15 Yrs
    4.98
    Motorcycle 50cc - Passengers KSI 16 Yrs
    6.31
    Motorcycle 50cc - Passengers KSI 17 Yrs
    7.78
    Motorcycle 50cc - Passengers KSI 18-19 Yrs
    25.63
    Motorcycle 50cc - Passengers KSI 20-24 Yrs
    38.25
    Motorcycle 50cc - Passengers KSI 25-29 Yrs
    27.15
    Motorcycle 50cc - Passengers KSI 30-39 Yrs
    24.9
    Motorcycle 50cc - Passengers KSI 40-49 Yrs
    50.5
    Motorcycle 50cc - Passengers KSI 50-59 Yrs
    27.01
    Motorcycle 50cc - Passengers KSI 60-69 Yrs
    19.6
    Motorcycle 50cc - Passengers KSI 70-79 Yrs
    2.34
    Motorcycle 50cc - Passengers KSI 80 + Yrs
    0.18
    Motorcycle 50cc - Passengers KSI Unknown
    4.95
    Car - Drivers Killed 0-4 Yrs
    0
    Car - Drivers Killed 5-7 Yrs
    0
    Car - Drivers Killed 8-11 Yrs
    0
    Car - Drivers Killed 12-15 Yrs
    0
    Car - Drivers Killed 16 Yrs
    0
    Car - Drivers Killed 17 Yrs
    1
    Car - Drivers Killed 18-19 Yrs
    44
    Car - Drivers Killed 20-24 Yrs
    86
    Car - Drivers Killed 25-29 Yrs
    70
    Car - Drivers Killed 30-39 Yrs
    62
    Car - Drivers Killed 40-49 Yrs
    60
    Car - Drivers Killed 50-59 Yrs
    59
    Car - Drivers Killed 60-69 Yrs
    49
    Car - Drivers Killed 70-79 Yrs
    58
    Car - Drivers Killed 80 + Yrs
    59
    Car - Drivers Killed Unknown
    0
    Car - Drivers KSI 0-4 Yrs
    0
    Car - Drivers KSI 5-7 Yrs
    0
    Car - Drivers KSI 8-11 Yrs
    0.02
    Car - Drivers KSI 12-15 Yrs
    1.24
    Car - Drivers KSI 16 Yrs
    5.43
    Car - Drivers KSI 17 Yrs
    110.14
    Car - Drivers KSI 18-19 Yrs
    449.32
    Car - Drivers KSI 20-24 Yrs
    1108.02
    Car - Drivers KSI 25-29 Yrs
    864.49
    Car - Drivers KSI 30-39 Yrs
    1116.12
    Car - Drivers KSI 40-49 Yrs
    1121.97
    Car - Drivers KSI 50-59 Yrs
    934.3
    Car - Drivers KSI 60-69 Yrs
    665.84
    Car - Drivers KSI 70-79 Yrs
    534.73
    Car - Drivers KSI 80 + Yrs
    434.12
    Car - Drivers KSI Unknown
    43.4
    Car - Passengers Killed 0-4 Yrs
    5
    Car - Passengers Killed 5-7 Yrs
    2
    Car - Passengers Killed 8-11 Yrs
    2
    Car - Passengers Killed 12-15 Yrs
    4
    Car - Passengers Killed 16 Yrs
    11
    Car - Passengers Killed 17 Yrs
    7
    Car - Passengers Killed 18-19 Yrs
    27
    Car - Passengers Killed 20-24 Yrs
    30
    Car - Passengers Killed 25-29 Yrs
    20
    Car - Passengers Killed 30-39 Yrs
    27
    Car - Passengers Killed 40-49 Yrs
    18
    Car - Passengers Killed 50-59 Yrs
    12
    Car - Passengers Killed 60-69 Yrs
    10
    Car - Passengers Killed 70-79 Yrs
    28
    Car - Passengers Killed 80 + Yrs
    34
    Car - Passengers Killed Unknown
    0
    Car - Passengers KSI 0-4 Yrs
    107.98
    Car - Passengers KSI 5-7 Yrs
    65.75
    Car - Passengers KSI 8-11 Yrs
    96.99
    Car - Passengers KSI 12-15 Yrs
    134.07
    Car - Passengers KSI 16 Yrs
    79.96
    Car - Passengers KSI 17 Yrs
    156.6
    Car - Passengers KSI 18-19 Yrs
    331.74
    Car - Passengers KSI 20-24 Yrs
    577.16
    Car - Passengers KSI 25-29 Yrs
    324.27
    Car - Passengers KSI 30-39 Yrs
    364.81
    Car - Passengers KSI 40-49 Yrs
    271.94
    Car - Passengers KSI 50-59 Yrs
    240.23
    Car - Passengers KSI 60-69 Yrs
    265.49
    Car - Passengers KSI 70-79 Yrs
    261.8
    Car - Passengers KSI 80 + Yrs
    210.47
    Car - Passengers KSI Unknown
    45.79
    Bus & coach Drivers Killed 0-4 Yrs
    0
    Bus & coach Drivers Killed 5-7 Yrs
    0
    Bus & coach Drivers Killed 8-11 Yrs
    0
    Bus & coach Drivers Killed 12-15 Yrs
    0
    Bus & coach Drivers Killed 16 Yrs
    0
    Bus & coach Drivers Killed 17 Yrs
    0
    Bus & coach Drivers Killed 18-19 Yrs
    0
    Bus & coach Drivers Killed 20-24 Yrs
    0
    Bus & coach Drivers Killed 25-29 Yrs
    0
    Bus & coach Drivers Killed 30-39 Yrs
    0
    Bus & coach Drivers Killed 40-49 Yrs
    0
    Bus & coach Drivers Killed 50-59 Yrs
    1
    Bus & coach Drivers Killed 60-69 Yrs
    1
    Bus & coach Drivers Killed 70-79 Yrs
    0
    Bus & coach Drivers Killed 80 + Yrs
    0
    Bus & coach Drivers Killed Unknown
    0
    Bus & coach Drivers KSI 0-4 Yrs
    0
    Bus & coach Drivers KSI 5-7 Yrs
    0
    Bus & coach Drivers KSI 8-11 Yrs
    0
    Bus & coach Drivers KSI 12-15 Yrs
    0
    Bus & coach Drivers KSI 16 Yrs
    0
    Bus & coach Drivers KSI 17 Yrs
    0.01
    Bus & coach Drivers KSI 18-19 Yrs
    0.01
    Bus & coach Drivers KSI 20-24 Yrs
    0.13
    Bus & coach Drivers KSI 25-29 Yrs
    2.42
    Bus & coach Drivers KSI 30-39 Yrs
    5.95
    Bus & coach Drivers KSI 40-49 Yrs
    7.43
    Bus & coach Drivers KSI 50-59 Yrs
    7.78
    Bus & coach Drivers KSI 60-69 Yrs
    5.07
    Bus & coach Drivers KSI 70-79 Yrs
    0.06
    Bus & coach Drivers KSI 80 + Yrs
    0.05
    Bus & coach Drivers KSI Unknown
    0.01
    Bus & coach Passengers Killed 0-4 Yrs
    0
    Bus & coach Passengers Killed 5-7 Yrs
    0
    Bus & coach Passengers Killed 8-11 Yrs
    0
    Bus & coach Passengers Killed 12-15 Yrs
    0
    Bus & coach Passengers Killed 16 Yrs
    0
    Bus & coach Passengers Killed 17 Yrs
    0
    Bus & coach Passengers Killed 18-19 Yrs
    1
    Bus & coach Passengers Killed 20-24 Yrs
    0
    Bus & coach Passengers Killed 25-29 Yrs
    0
    Bus & coach Passengers Killed 30-39 Yrs
    0
    Bus & coach Passengers Killed 40-49 Yrs
    1
    Bus & coach Passengers Killed 50-59 Yrs
    0
    Bus & coach Passengers Killed 60-69 Yrs
    1
    Bus & coach Passengers Killed 70-79 Yrs
    1
    Bus & coach Passengers Killed 80 + Yrs
    4
    Bus & coach Passengers Killed Unknown
    0
    Bus & coach Passengers KSI 0-4 Yrs
    5.06
    Bus & coach Passengers KSI 5-7 Yrs
    1.62
    Bus & coach Passengers KSI 8-11 Yrs
    6.14
    Bus & coach Passengers KSI 12-15 Yrs
    11.53
    Bus & coach Passengers KSI 16 Yrs
    1.28
    Bus & coach Passengers KSI 17 Yrs
    2.97
    Bus & coach Passengers KSI 18-19 Yrs
    5.23
    Bus & coach Passengers KSI 20-24 Yrs
    16.51
    Bus & coach Passengers KSI 25-29 Yrs
    15.57
    Bus & coach Passengers KSI 30-39 Yrs
    30.65
    Bus & coach Passengers KSI 40-49 Yrs
    24.33
    Bus & coach Passengers KSI 50-59 Yrs
    41.04
    Bus & coach Passengers KSI 60-69 Yrs
    58.44
    Bus & coach Passengers KSI 70-79 Yrs
    82.59
    Bus & coach Passengers KSI 80 + Yrs
    75.09
    Bus & coach Passengers KSI Unknown
    13.72
    Goods vehicle Drivers Killed 0-4 Yrs
    0
    Goods vehicle Drivers Killed 5-7 Yrs
    0
    Goods vehicle Drivers Killed 8-11 Yrs
    0
    Goods vehicle Drivers Killed 12-15 Yrs
    0
    Goods vehicle Drivers Killed 16 Yrs
    0
    Goods vehicle Drivers Killed 17 Yrs
    0
    Goods vehicle Drivers Killed 18-19 Yrs
    1
    Goods vehicle Drivers Killed 20-24 Yrs
    8
    Goods vehicle Drivers Killed 25-29 Yrs
    2
    Goods vehicle Drivers Killed 30-39 Yrs
    6
    Goods vehicle Drivers Killed 40-49 Yrs
    9
    Goods vehicle Drivers Killed 50-59 Yrs
    12
    Goods vehicle Drivers Killed 60-69 Yrs
    6
    Goods vehicle Drivers Killed 70-79 Yrs
    2
    Goods vehicle Drivers Killed 80 + Yrs
    0
    Goods vehicle Drivers Killed Unknown
    0
    Goods vehicle Drivers KSI 0-4 Yrs
    0
    Goods vehicle Drivers KSI 5-7 Yrs
    0
    Goods vehicle Drivers KSI 8-11 Yrs
    0
    Goods vehicle Drivers KSI 12-15 Yrs
    0.04
    Goods vehicle Drivers KSI 16 Yrs
    0.11
    Goods vehicle Drivers KSI 17 Yrs
    0.29
    Goods vehicle Drivers KSI 18-19 Yrs
    4.8
    Goods vehicle Drivers KSI 20-24 Yrs
    53.12
    Goods vehicle Drivers KSI 25-29 Yrs
    49.23
    Goods vehicle Drivers KSI 30-39 Yrs
    112.8
    Goods vehicle Drivers KSI 40-49 Yrs
    140.35
    Goods vehicle Drivers KSI 50-59 Yrs
    139.42
    Goods vehicle Drivers KSI 60-69 Yrs
    50.69
    Goods vehicle Drivers KSI 70-79 Yrs
    11.99
    Goods vehicle Drivers KSI 80 + Yrs
    2.57
    Goods vehicle Drivers KSI Unknown
    3.29
    Goods vehicle Passengers Killed 0-4 Yrs
    0
    Goods vehicle Passengers Killed 5-7 Yrs
    0
    Goods vehicle Passengers Killed 8-11 Yrs
    0
    Goods vehicle Passengers Killed 12-15 Yrs
    1
    Goods vehicle Passengers Killed 16 Yrs
    0
    Goods vehicle Passengers Killed 17 Yrs
    1
    Goods vehicle Passengers Killed 18-19 Yrs
    2
    Goods vehicle Passengers Killed 20-24 Yrs
    4
    Goods vehicle Passengers Killed 25-29 Yrs
    1
    Goods vehicle Passengers Killed 30-39 Yrs
    1
    Goods vehicle Passengers Killed 40-49 Yrs
    2
    Goods vehicle Passengers Killed 50-59 Yrs
    0
    Goods vehicle Passengers Killed 60-69 Yrs
    0
    Goods vehicle Passengers Killed 70-79 Yrs
    0
    Goods vehicle Passengers Killed 80 + Yrs
    0
    Goods vehicle Passengers Killed Unknown
    0
    Goods vehicle Passengers KSI 0-4 Yrs
    1.36
    Goods vehicle Passengers KSI 5-7 Yrs
    1.54
    Goods vehicle Passengers KSI 8-11 Yrs
    1.22
    Goods vehicle Passengers KSI 12-15 Yrs
    2.91
    Goods vehicle Passengers KSI 16 Yrs
    1.17
    Goods vehicle Passengers KSI 17 Yrs
    1.4
    Goods vehicle Passengers KSI 18-19 Yrs
    7.86
    Goods vehicle Passengers KSI 20-24 Yrs
    30.82
    Goods vehicle Passengers KSI 25-29 Yrs
    13.28
    Goods vehicle Passengers KSI 30-39 Yrs
    23.42
    Goods vehicle Passengers KSI 40-49 Yrs
    26.44
    Goods vehicle Passengers KSI 50-59 Yrs
    14.86
    Goods vehicle Passengers KSI 60-69 Yrs
    3.67
    Goods vehicle Passengers KSI 70-79 Yrs
    1.37
    Goods vehicle Passengers KSI 80 + Yrs
    0.35
    Goods vehicle Passengers KSI Unknown
    4.47
    Other vehicle/Unknown vehicle occupants Killed 0-4 Yrs
    0
    Other vehicle/Unknown vehicle occupants Killed 5-7 Yrs
    0
    Other vehicle/Unknown vehicle occupants Killed 8-11 Yrs
    0
    Other vehicle/Unknown vehicle occupants Killed 12-15 Yrs
    0
    Other vehicle/Unknown vehicle occupants Killed 16 Yrs
    0
    Other vehicle/Unknown vehicle occupants Killed 17 Yrs
    0
    Other vehicle/Unknown vehicle occupants Killed 18-19 Yrs
    1
    Other vehicle/Unknown vehicle occupants Killed 20-24 Yrs
    1
    Other vehicle/Unknown vehicle occupants Killed 25-29 Yrs
    1
    Other vehicle/Unknown vehicle occupants Killed 30-39 Yrs
    0
    Other vehicle/Unknown vehicle occupants Killed 40-49 Yrs
    2
    Other vehicle/Unknown vehicle occupants Killed 50-59 Yrs
    5
    Other vehicle/Unknown vehicle occupants Killed 60-69 Yrs
    2
    Other vehicle/Unknown vehicle occupants Killed 70-79 Yrs
    5
    Other vehicle/Unknown vehicle occupants Killed 80 + Yrs
    5
    Other vehicle/Unknown vehicle occupants Killed Unknown
    0
    Other vehicle/Unknown vehicle occupants KSI 0-4 Yrs
    1.14
    Other vehicle/Unknown vehicle occupants KSI 5-7 Yrs
    0.03
    Other vehicle/Unknown vehicle occupants KSI 8-11 Yrs
    1.88
    Other vehicle/Unknown vehicle occupants KSI 12-15 Yrs
    5.71
    Other vehicle/Unknown vehicle occupants KSI 16 Yrs
    1.19
    Other vehicle/Unknown vehicle occupants KSI 17 Yrs
    6.36
    Other vehicle/Unknown vehicle occupants KSI 18-19 Yrs
    15.07
    Other vehicle/Unknown vehicle occupants KSI 20-24 Yrs
    15.29
    Other vehicle/Unknown vehicle occupants KSI 25-29 Yrs
    22.05
    Other vehicle/Unknown vehicle occupants KSI 30-39 Yrs
    37.54
    Other vehicle/Unknown vehicle occupants KSI 40-49 Yrs
    38.01
    Other vehicle/Unknown vehicle occupants KSI 50-59 Yrs
    30.28
    Other vehicle/Unknown vehicle occupants KSI 60-69 Yrs
    26.78
    Other vehicle/Unknown vehicle occupants KSI 70-79 Yrs
    25.25
    Other vehicle/Unknown vehicle occupants KSI 80 + Yrs
    32.1
    Other vehicle/Unknown vehicle occupants KSI Unknown
    2.39
    All road users Killed 0-4 Yrs
    10
    All road users Killed 5-7 Yrs
    6
    All road users Killed 8-11 Yrs
    10
    All road users Killed 12-15 Yrs
    22
    All road users Killed 16 Yrs
    17
    All road users Killed 17 Yrs
    20
    All road users Killed 18-19 Yrs
    110
    All road users Killed 20-24 Yrs
    225
    All road users Killed 25-29 Yrs
    165
    All road users Killed 30-39 Yrs
    202
    All road users Killed 40-49 Yrs
    254
    All road users Killed 50-59 Yrs
    213
    All road users Killed 60-69 Yrs
    131
    All road users Killed 70-79 Yrs
    155
    All road users Killed 80 + Yrs
    173
    All road users Killed Unknown
    0
    All road users KSI 0-4 Yrs
    347.76
    All road users KSI 5-7 Yrs
    389.63
    All road users KSI 8-11 Yrs
    722.99
    All road users KSI 12-15 Yrs
    1021.82
    All road users KSI 16 Yrs
    454.42
    All road users KSI 17 Yrs
    754.6
    All road users KSI 18-19 Yrs
    1681.29
    All road users KSI 20-24 Yrs
    3686.42
    All road users KSI 25-29 Yrs
    2752.31
    All road users KSI 30-39 Yrs
    4044.53
    All road users KSI 40-49 Yrs
    4359.14
    All road users KSI 50-59 Yrs
    3393.01
    All road users KSI 60-69 Yrs
    2142.78
    All road users KSI 70-79 Yrs
    1556.42
    All road users KSI 80 + Yrs
    1274.67
    All road users KSI Unknown
    311.22


    (this is from DataModel)
    I see decimals, where is the problem?
    Last edited by sandy666; Oct 13th, 2019 at 01:23 PM.
    I know you know but I forgot my Crystal Ball and don't know what you know



    In the first post, show the type of machine (PC / Mac) and the Office version you are working on
    I don't use vba in any form!

  3. #3
    New Member
    Join Date
    Jan 2011
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: In Power Query - Decimals getting removed

    Hi

    I have created a video of the steps I took. I hope this explains it a bit better.

    As you will see decimals get turned into 'general' and then when I un-pivot the table everything goes to zero

    https://youtu.be/tNQRx6-vuho








    Quote Originally Posted by mwoolgar View Post
    Hi All

    I've been playing with the data below. I'm trying to unpivot the age ranges in power query.

    When I do this and then try to load it to the data model all the decimals get removed. Instead of decimals I just get zeros.

    What am I doing wrong?

    Many thanks

    Miles


    User Type Severity 0-4 Yrs 5-7 Yrs 8-11 Yrs 12-15 Yrs 16 Yrs 17 Yrs 18-19 Yrs 20-24 Yrs 25-29 Yrs 30-39 Yrs 40-49 Yrs 50-59 Yrs 60-69 Yrs 70-79 Yrs 80 + Yrs Unknown
    Pedestrians Killed 5.00 3.00 6.00 12.00 4.00 5.00 14.00 33.00 29.00 44.00 49.00 48.00 31.00 47.00 68.00 0.00
    Pedestrians KSI 227.76 286.25 517.00 610.25 121.96 107.56 237.17 500.14 438.78 653.73 675.00 579.03 503.17 471.39 475.23 83.10
    Pedal cyclists Killed 0.00 1.00 1.00 4.00 1.00 2.00 3.00 10.00 5.00 15.00 18.00 24.00 14.00 8.00 3.00 0.00
    Pedal cyclists KSI 4.47 34.32 95.59 230.20 65.08 68.21 101.05 354.78 400.25 779.62 854.73 554.06 227.60 109.08 32.85 64.14
    Motorcycle 50cc and under Killed 0.00 0.00 0.00 0.00 1.00 1.00 0.00 0.00 0.00 1.00 0.00 0.00 0.00 0.00 0.00 0.00
    Motorcycle 50cc and under KSI 0.00 0.02 0.16 7.93 148.10 90.56 62.80 72.51 35.68 47.89 39.45 24.15 10.74 3.45 2.22 4.68
    Motorcycle 50cc - riders Killed 0.00 0.00 0.00 1.00 0.00 3.00 15.00 52.00 36.00 45.00 90.00 52.00 16.00 6.00 0.00 0.00
    Motorcycle 50cc - riders KSI 0.00 0.00 0.00 12.96 23.83 202.72 440.61 919.68 559.13 847.10 1108.99 800.87 305.70 52.37 9.45 41.28
    Motorcycle 50cc - Passengers Killed 0.00 0.00 1.00 0.00 0.00 0.00 2.00 1.00 1.00 1.00 5.00 0.00 1.00 0.00 0.00 0.00
    Motorcycle 50cc - Passengers KSI 0.00 0.10 3.98 4.98 6.31 7.78 25.63 38.25 27.15 24.90 50.50 27.01 19.60 2.34 0.18 4.95
    Car - Drivers Killed 0.00 0.00 0.00 0.00 0.00 1.00 44.00 86.00 70.00 62.00 60.00 59.00 49.00 58.00 59.00 0.00
    Car - Drivers KSI 0.00 0.00 0.02 1.24 5.43 110.14 449.32 1108.02 864.49 1116.12 1121.97 934.30 665.84 534.73 434.12 43.40
    Car - Passengers Killed 5.00 2.00 2.00 4.00 11.00 7.00 27.00 30.00 20.00 27.00 18.00 12.00 10.00 28.00 34.00 0.00
    Car - Passengers KSI 107.98 65.75 96.99 134.07 79.96 156.60 331.74 577.16 324.27 364.81 271.94 240.23 265.49 261.80 210.47 45.79
    Bus & coach Drivers Killed 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 1.00 1.00 0.00 0.00 0.00
    Bus & coach Drivers KSI 0.00 0.00 0.00 0.00 0.00 0.01 0.01 0.13 2.42 5.95 7.43 7.78 5.07 0.06 0.05 0.01
    Bus & coach Passengers Killed 0.00 0.00 0.00 0.00 0.00 0.00 1.00 0.00 0.00 0.00 1.00 0.00 1.00 1.00 4.00 0.00
    Bus & coach Passengers KSI 5.06 1.62 6.14 11.53 1.28 2.97 5.23 16.51 15.57 30.65 24.33 41.04 58.44 82.59 75.09 13.72
    Goods vehicle Drivers Killed 0.00 0.00 0.00 0.00 0.00 0.00 1.00 8.00 2.00 6.00 9.00 12.00 6.00 2.00 0.00 0.00
    Goods vehicle Drivers KSI 0.00 0.00 0.00 0.04 0.11 0.29 4.80 53.12 49.23 112.80 140.35 139.42 50.69 11.99 2.57 3.29
    Goods vehicle Passengers Killed 0.00 0.00 0.00 1.00 0.00 1.00 2.00 4.00 1.00 1.00 2.00 0.00 0.00 0.00 0.00 0.00
    Goods vehicle Passengers KSI 1.36 1.54 1.22 2.91 1.17 1.40 7.86 30.82 13.28 23.42 26.44 14.86 3.67 1.37 0.35 4.47
    Other vehicle/Unknown vehicle occupants Killed 0.00 0.00 0.00 0.00 0.00 0.00 1.00 1.00 1.00 0.00 2.00 5.00 2.00 5.00 5.00 0.00
    Other vehicle/Unknown vehicle occupants KSI 1.14 0.03 1.88 5.71 1.19 6.36 15.07 15.29 22.05 37.54 38.01 30.28 26.78 25.25 32.10 2.39
    All road users Killed 10.00 6.00 10.00 22.00 17.00 20.00 110.00 225.00 165.00 202.00 254.00 213.00 131.00 155.00 173.00 0.00
    All road users KSI 347.76 389.63 722.99 1021.82 454.42 754.60 1681.29 3686.42 2752.31 4044.53 4359.14 3393.01 2142.78 1556.42 1274.67 311.22

  4. #4
    Board Regular
    Join Date
    Apr 2016
    Posts
    73
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: In Power Query - Decimals getting removed

    Quote Originally Posted by mwoolgar View Post
    Hi All

    I've been playing with the data below. I'm trying to unpivot the age ranges in power query.

    When I do this and then try to load it to the data model all the decimals get removed. Instead of decimals I just get zeros.

    What am I doing wrong?

    Many thanks

    Miles


    User Type Severity 0-4 Yrs 5-7 Yrs 8-11 Yrs 12-15 Yrs 16 Yrs 17 Yrs 18-19 Yrs 20-24 Yrs 25-29 Yrs 30-39 Yrs 40-49 Yrs 50-59 Yrs 60-69 Yrs 70-79 Yrs 80 + Yrs Unknown
    Pedestrians Killed 5.00 3.00 6.00 12.00 4.00 5.00 14.00 33.00 29.00 44.00 49.00 48.00 31.00 47.00 68.00 0.00
    Pedestrians KSI 227.76 286.25 517.00 610.25 121.96 107.56 237.17 500.14 438.78 653.73 675.00 579.03 503.17 471.39 475.23 83.10
    Pedal cyclists Killed 0.00 1.00 1.00 4.00 1.00 2.00 3.00 10.00 5.00 15.00 18.00 24.00 14.00 8.00 3.00 0.00
    Pedal cyclists KSI 4.47 34.32 95.59 230.20 65.08 68.21 101.05 354.78 400.25 779.62 854.73 554.06 227.60 109.08 32.85 64.14
    Motorcycle 50cc and under Killed 0.00 0.00 0.00 0.00 1.00 1.00 0.00 0.00 0.00 1.00 0.00 0.00 0.00 0.00 0.00 0.00
    Motorcycle 50cc and under KSI 0.00 0.02 0.16 7.93 148.10 90.56 62.80 72.51 35.68 47.89 39.45 24.15 10.74 3.45 2.22 4.68
    Motorcycle 50cc - riders Killed 0.00 0.00 0.00 1.00 0.00 3.00 15.00 52.00 36.00 45.00 90.00 52.00 16.00 6.00 0.00 0.00
    Motorcycle 50cc - riders KSI 0.00 0.00 0.00 12.96 23.83 202.72 440.61 919.68 559.13 847.10 1108.99 800.87 305.70 52.37 9.45 41.28
    Motorcycle 50cc - Passengers Killed 0.00 0.00 1.00 0.00 0.00 0.00 2.00 1.00 1.00 1.00 5.00 0.00 1.00 0.00 0.00 0.00
    Motorcycle 50cc - Passengers KSI 0.00 0.10 3.98 4.98 6.31 7.78 25.63 38.25 27.15 24.90 50.50 27.01 19.60 2.34 0.18 4.95
    Car - Drivers Killed 0.00 0.00 0.00 0.00 0.00 1.00 44.00 86.00 70.00 62.00 60.00 59.00 49.00 58.00 59.00 0.00
    Car - Drivers KSI 0.00 0.00 0.02 1.24 5.43 110.14 449.32 1108.02 864.49 1116.12 1121.97 934.30 665.84 534.73 434.12 43.40
    Car - Passengers Killed 5.00 2.00 2.00 4.00 11.00 7.00 27.00 30.00 20.00 27.00 18.00 12.00 10.00 28.00 34.00 0.00
    Car - Passengers KSI 107.98 65.75 96.99 134.07 79.96 156.60 331.74 577.16 324.27 364.81 271.94 240.23 265.49 261.80 210.47 45.79
    Bus & coach Drivers Killed 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 1.00 1.00 0.00 0.00 0.00
    Bus & coach Drivers KSI 0.00 0.00 0.00 0.00 0.00 0.01 0.01 0.13 2.42 5.95 7.43 7.78 5.07 0.06 0.05 0.01
    Bus & coach Passengers Killed 0.00 0.00 0.00 0.00 0.00 0.00 1.00 0.00 0.00 0.00 1.00 0.00 1.00 1.00 4.00 0.00
    Bus & coach Passengers KSI 5.06 1.62 6.14 11.53 1.28 2.97 5.23 16.51 15.57 30.65 24.33 41.04 58.44 82.59 75.09 13.72
    Goods vehicle Drivers Killed 0.00 0.00 0.00 0.00 0.00 0.00 1.00 8.00 2.00 6.00 9.00 12.00 6.00 2.00 0.00 0.00
    Goods vehicle Drivers KSI 0.00 0.00 0.00 0.04 0.11 0.29 4.80 53.12 49.23 112.80 140.35 139.42 50.69 11.99 2.57 3.29
    Goods vehicle Passengers Killed 0.00 0.00 0.00 1.00 0.00 1.00 2.00 4.00 1.00 1.00 2.00 0.00 0.00 0.00 0.00 0.00
    Goods vehicle Passengers KSI 1.36 1.54 1.22 2.91 1.17 1.40 7.86 30.82 13.28 23.42 26.44 14.86 3.67 1.37 0.35 4.47
    Other vehicle/Unknown vehicle occupants Killed 0.00 0.00 0.00 0.00 0.00 0.00 1.00 1.00 1.00 0.00 2.00 5.00 2.00 5.00 5.00 0.00
    Other vehicle/Unknown vehicle occupants KSI 1.14 0.03 1.88 5.71 1.19 6.36 15.07 15.29 22.05 37.54 38.01 30.28 26.78 25.25 32.10 2.39
    All road users Killed 10.00 6.00 10.00 22.00 17.00 20.00 110.00 225.00 165.00 202.00 254.00 213.00 131.00 155.00 173.00 0.00
    All road users KSI 347.76 389.63 722.99 1021.82 454.42 754.60 1681.29 3686.42 2752.31 4044.53 4359.14 3393.01 2142.78 1556.42 1274.67 311.22
    Keep your data format to DECIMAL

  5. #5
    Board Regular sandy666's Avatar
    Join Date
    Oct 2015
    Posts
    3,027
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    1 Thread(s)

    Cool Re: In Power Query - Decimals getting removed

    add your table directly to Data Model from Power Query without loading any Query Table into the sheet



    Don't quote whole post, it doesn't make any sense. Use Reply instead of Reply With Quote
    Last edited by sandy666; Oct 13th, 2019 at 07:45 PM.
    I know you know but I forgot my Crystal Ball and don't know what you know



    In the first post, show the type of machine (PC / Mac) and the Office version you are working on
    I don't use vba in any form!

  6. #6
    New Member
    Join Date
    Jan 2011
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: In Power Query - Decimals getting removed

    Quote Originally Posted by arunsjain View Post
    Keep your data format to DECIMAL
    Yes, I've tried that. No matter what I do it still does what I have shown in the video

  7. #7
    Board Regular sandy666's Avatar
    Join Date
    Oct 2015
    Posts
    3,027
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    1 Thread(s)

    Default Re: In Power Query - Decimals getting removed

    see post #5

    btw. why you loading Query Table into Data Model?
    If you want create Pivot Table you don't need a Data Model, enough Unpivoted Query Table and standard Insert - Pivot Table
    I know you know but I forgot my Crystal Ball and don't know what you know



    In the first post, show the type of machine (PC / Mac) and the Office version you are working on
    I don't use vba in any form!

  8. #8
    New Member
    Join Date
    Jan 2011
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: In Power Query - Decimals getting removed

    Quote Originally Posted by sandy666 View Post
    see post #5

    btw. why you loading Query Table into Data Model?
    If you want create Pivot Table you don't need a Data Model, enough Unpivoted Query Table and standard Insert - Pivot Table
    No reason particularly - just messing about with power query

  9. #9
    New Member
    Join Date
    Jan 2011
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: In Power Query - Decimals getting removed

    My decidmal values still turn to zeros when I unpivot and load the data using just a connection and add to the data model.

    If I don't unpivot, my figures remain in the data model.
    Last edited by mwoolgar; Oct 14th, 2019 at 07:47 AM.

  10. #10
    Board Regular sandy666's Avatar
    Join Date
    Oct 2015
    Posts
    3,027
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    1 Thread(s)

    Cool Re: In Power Query - Decimals getting removed

    check this file

    PQ unpivot to data model
    Last edited by sandy666; Oct 14th, 2019 at 07:56 AM.
    I know you know but I forgot my Crystal Ball and don't know what you know



    In the first post, show the type of machine (PC / Mac) and the Office version you are working on
    I don't use vba in any form!

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •