In Power Query - Decimals getting removed

mwoolgar

New Member
Joined
Jan 25, 2011
Messages
11
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 TypeSeverity0-4 Yrs5-7 Yrs8-11 Yrs12-15 Yrs16 Yrs17 Yrs18-19 Yrs20-24 Yrs25-29 Yrs30-39 Yrs40-49 Yrs50-59 Yrs60-69 Yrs70-79 Yrs80 + YrsUnknown
PedestriansKilled5.003.006.0012.004.005.0014.0033.0029.0044.0049.0048.0031.0047.0068.000.00
PedestriansKSI227.76286.25517.00610.25121.96107.56237.17500.14438.78653.73675.00579.03503.17471.39475.2383.10
Pedal cyclistsKilled0.001.001.004.001.002.003.0010.005.0015.0018.0024.0014.008.003.000.00
Pedal cyclistsKSI4.4734.3295.59230.2065.0868.21101.05354.78400.25779.62854.73554.06227.60109.0832.8564.14
Motorcycle 50cc and underKilled0.000.000.000.001.001.000.000.000.001.000.000.000.000.000.000.00
Motorcycle 50cc and underKSI0.000.020.167.93148.1090.5662.8072.5135.6847.8939.4524.1510.743.452.224.68
Motorcycle 50cc - ridersKilled0.000.000.001.000.003.0015.0052.0036.0045.0090.0052.0016.006.000.000.00
Motorcycle 50cc - ridersKSI0.000.000.0012.9623.83202.72440.61919.68559.13847.101108.99800.87305.7052.379.4541.28
Motorcycle 50cc - PassengersKilled0.000.001.000.000.000.002.001.001.001.005.000.001.000.000.000.00
Motorcycle 50cc - PassengersKSI0.000.103.984.986.317.7825.6338.2527.1524.9050.5027.0119.602.340.184.95
Car - DriversKilled0.000.000.000.000.001.0044.0086.0070.0062.0060.0059.0049.0058.0059.000.00
Car - DriversKSI0.000.000.021.245.43110.14449.321108.02864.491116.121121.97934.30665.84534.73434.1243.40
Car - PassengersKilled5.002.002.004.0011.007.0027.0030.0020.0027.0018.0012.0010.0028.0034.000.00
Car - PassengersKSI107.9865.7596.99134.0779.96156.60331.74577.16324.27364.81271.94240.23265.49261.80210.4745.79
Bus & coach DriversKilled0.000.000.000.000.000.000.000.000.000.000.001.001.000.000.000.00
Bus & coach DriversKSI0.000.000.000.000.000.010.010.132.425.957.437.785.070.060.050.01
Bus & coach PassengersKilled0.000.000.000.000.000.001.000.000.000.001.000.001.001.004.000.00
Bus & coach PassengersKSI5.061.626.1411.531.282.975.2316.5115.5730.6524.3341.0458.4482.5975.0913.72
Goods vehicle DriversKilled0.000.000.000.000.000.001.008.002.006.009.0012.006.002.000.000.00
Goods vehicle DriversKSI0.000.000.000.040.110.294.8053.1249.23112.80140.35139.4250.6911.992.573.29
Goods vehicle PassengersKilled0.000.000.001.000.001.002.004.001.001.002.000.000.000.000.000.00
Goods vehicle PassengersKSI1.361.541.222.911.171.407.8630.8213.2823.4226.4414.863.671.370.354.47
Other vehicle/Unknown vehicle occupantsKilled0.000.000.000.000.000.001.001.001.000.002.005.002.005.005.000.00
Other vehicle/Unknown vehicle occupantsKSI1.140.031.885.711.196.3615.0715.2922.0537.5438.0130.2826.7825.2532.102.39
All road usersKilled10.006.0010.0022.0017.0020.00110.00225.00165.00202.00254.00213.00131.00155.00173.000.00
All road usersKSI347.76389.63722.991021.82454.42754.601681.293686.422752.314044.534359.143393.012142.781556.421274.67311.22

<tbody>
</tbody>
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
User TypeSeverityAttributeValue
PedestriansKilled0-4 Yrs
5​
PedestriansKilled5-7 Yrs
3​
PedestriansKilled8-11 Yrs
6​
PedestriansKilled12-15 Yrs
12​
PedestriansKilled16 Yrs
4​
PedestriansKilled17 Yrs
5​
PedestriansKilled18-19 Yrs
14​
PedestriansKilled20-24 Yrs
33​
PedestriansKilled25-29 Yrs
29​
PedestriansKilled30-39 Yrs
44​
PedestriansKilled40-49 Yrs
49​
PedestriansKilled50-59 Yrs
48​
PedestriansKilled60-69 Yrs
31​
PedestriansKilled70-79 Yrs
47​
PedestriansKilled80 + Yrs
68​
PedestriansKilledUnknown
0​
PedestriansKSI0-4 Yrs
227.76​
PedestriansKSI5-7 Yrs
286.25​
PedestriansKSI8-11 Yrs
517​
PedestriansKSI12-15 Yrs
610.25​
PedestriansKSI16 Yrs
121.96​
PedestriansKSI17 Yrs
107.56​
PedestriansKSI18-19 Yrs
237.17​
PedestriansKSI20-24 Yrs
500.14​
PedestriansKSI25-29 Yrs
438.78​
PedestriansKSI30-39 Yrs
653.73​
PedestriansKSI40-49 Yrs
675​
PedestriansKSI50-59 Yrs
579.03​
PedestriansKSI60-69 Yrs
503.17​
PedestriansKSI70-79 Yrs
471.39​
PedestriansKSI80 + Yrs
475.23​
PedestriansKSIUnknown
83.1​
Pedal cyclistsKilled0-4 Yrs
0​
Pedal cyclistsKilled5-7 Yrs
1​
Pedal cyclistsKilled8-11 Yrs
1​
Pedal cyclistsKilled12-15 Yrs
4​
Pedal cyclistsKilled16 Yrs
1​
Pedal cyclistsKilled17 Yrs
2​
Pedal cyclistsKilled18-19 Yrs
3​
Pedal cyclistsKilled20-24 Yrs
10​
Pedal cyclistsKilled25-29 Yrs
5​
Pedal cyclistsKilled30-39 Yrs
15​
Pedal cyclistsKilled40-49 Yrs
18​
Pedal cyclistsKilled50-59 Yrs
24​
Pedal cyclistsKilled60-69 Yrs
14​
Pedal cyclistsKilled70-79 Yrs
8​
Pedal cyclistsKilled80 + Yrs
3​
Pedal cyclistsKilledUnknown
0​
Pedal cyclistsKSI0-4 Yrs
4.47​
Pedal cyclistsKSI5-7 Yrs
34.32​
Pedal cyclistsKSI8-11 Yrs
95.59​
Pedal cyclistsKSI12-15 Yrs
230.2​
Pedal cyclistsKSI16 Yrs
65.08​
Pedal cyclistsKSI17 Yrs
68.21​
Pedal cyclistsKSI18-19 Yrs
101.05​
Pedal cyclistsKSI20-24 Yrs
354.78​
Pedal cyclistsKSI25-29 Yrs
400.25​
Pedal cyclistsKSI30-39 Yrs
779.62​
Pedal cyclistsKSI40-49 Yrs
854.73​
Pedal cyclistsKSI50-59 Yrs
554.06​
Pedal cyclistsKSI60-69 Yrs
227.6​
Pedal cyclistsKSI70-79 Yrs
109.08​
Pedal cyclistsKSI80 + Yrs
32.85​
Pedal cyclistsKSIUnknown
64.14​
Motorcycle 50cc and underKilled0-4 Yrs
0​
Motorcycle 50cc and underKilled5-7 Yrs
0​
Motorcycle 50cc and underKilled8-11 Yrs
0​
Motorcycle 50cc and underKilled12-15 Yrs
0​
Motorcycle 50cc and underKilled16 Yrs
1​
Motorcycle 50cc and underKilled17 Yrs
1​
Motorcycle 50cc and underKilled18-19 Yrs
0​
Motorcycle 50cc and underKilled20-24 Yrs
0​
Motorcycle 50cc and underKilled25-29 Yrs
0​
Motorcycle 50cc and underKilled30-39 Yrs
1​
Motorcycle 50cc and underKilled40-49 Yrs
0​
Motorcycle 50cc and underKilled50-59 Yrs
0​
Motorcycle 50cc and underKilled60-69 Yrs
0​
Motorcycle 50cc and underKilled70-79 Yrs
0​
Motorcycle 50cc and underKilled80 + Yrs
0​
Motorcycle 50cc and underKilledUnknown
0​
Motorcycle 50cc and underKSI0-4 Yrs
0​
Motorcycle 50cc and underKSI5-7 Yrs
0.02​
Motorcycle 50cc and underKSI8-11 Yrs
0.16​
Motorcycle 50cc and underKSI12-15 Yrs
7.93​
Motorcycle 50cc and underKSI16 Yrs
148.1​
Motorcycle 50cc and underKSI17 Yrs
90.56​
Motorcycle 50cc and underKSI18-19 Yrs
62.8​
Motorcycle 50cc and underKSI20-24 Yrs
72.51​
Motorcycle 50cc and underKSI25-29 Yrs
35.68​
Motorcycle 50cc and underKSI30-39 Yrs
47.89​
Motorcycle 50cc and underKSI40-49 Yrs
39.45​
Motorcycle 50cc and underKSI50-59 Yrs
24.15​
Motorcycle 50cc and underKSI60-69 Yrs
10.74​
Motorcycle 50cc and underKSI70-79 Yrs
3.45​
Motorcycle 50cc and underKSI80 + Yrs
2.22​
Motorcycle 50cc and underKSIUnknown
4.68​
Motorcycle 50cc - ridersKilled0-4 Yrs
0​
Motorcycle 50cc - ridersKilled5-7 Yrs
0​
Motorcycle 50cc - ridersKilled8-11 Yrs
0​
Motorcycle 50cc - ridersKilled12-15 Yrs
1​
Motorcycle 50cc - ridersKilled16 Yrs
0​
Motorcycle 50cc - ridersKilled17 Yrs
3​
Motorcycle 50cc - ridersKilled18-19 Yrs
15​
Motorcycle 50cc - ridersKilled20-24 Yrs
52​
Motorcycle 50cc - ridersKilled25-29 Yrs
36​
Motorcycle 50cc - ridersKilled30-39 Yrs
45​
Motorcycle 50cc - ridersKilled40-49 Yrs
90​
Motorcycle 50cc - ridersKilled50-59 Yrs
52​
Motorcycle 50cc - ridersKilled60-69 Yrs
16​
Motorcycle 50cc - ridersKilled70-79 Yrs
6​
Motorcycle 50cc - ridersKilled80 + Yrs
0​
Motorcycle 50cc - ridersKilledUnknown
0​
Motorcycle 50cc - ridersKSI0-4 Yrs
0​
Motorcycle 50cc - ridersKSI5-7 Yrs
0​
Motorcycle 50cc - ridersKSI8-11 Yrs
0​
Motorcycle 50cc - ridersKSI12-15 Yrs
12.96​
Motorcycle 50cc - ridersKSI16 Yrs
23.83​
Motorcycle 50cc - ridersKSI17 Yrs
202.72​
Motorcycle 50cc - ridersKSI18-19 Yrs
440.61​
Motorcycle 50cc - ridersKSI20-24 Yrs
919.68​
Motorcycle 50cc - ridersKSI25-29 Yrs
559.13​
Motorcycle 50cc - ridersKSI30-39 Yrs
847.1​
Motorcycle 50cc - ridersKSI40-49 Yrs
1108.99​
Motorcycle 50cc - ridersKSI50-59 Yrs
800.87​
Motorcycle 50cc - ridersKSI60-69 Yrs
305.7​
Motorcycle 50cc - ridersKSI70-79 Yrs
52.37​
Motorcycle 50cc - ridersKSI80 + Yrs
9.45​
Motorcycle 50cc - ridersKSIUnknown
41.28​
Motorcycle 50cc - PassengersKilled0-4 Yrs
0​
Motorcycle 50cc - PassengersKilled5-7 Yrs
0​
Motorcycle 50cc - PassengersKilled8-11 Yrs
1​
Motorcycle 50cc - PassengersKilled12-15 Yrs
0​
Motorcycle 50cc - PassengersKilled16 Yrs
0​
Motorcycle 50cc - PassengersKilled17 Yrs
0​
Motorcycle 50cc - PassengersKilled18-19 Yrs
2​
Motorcycle 50cc - PassengersKilled20-24 Yrs
1​
Motorcycle 50cc - PassengersKilled25-29 Yrs
1​
Motorcycle 50cc - PassengersKilled30-39 Yrs
1​
Motorcycle 50cc - PassengersKilled40-49 Yrs
5​
Motorcycle 50cc - PassengersKilled50-59 Yrs
0​
Motorcycle 50cc - PassengersKilled60-69 Yrs
1​
Motorcycle 50cc - PassengersKilled70-79 Yrs
0​
Motorcycle 50cc - PassengersKilled80 + Yrs
0​
Motorcycle 50cc - PassengersKilledUnknown
0​
Motorcycle 50cc - PassengersKSI0-4 Yrs
0​
Motorcycle 50cc - PassengersKSI5-7 Yrs
0.1​
Motorcycle 50cc - PassengersKSI8-11 Yrs
3.98​
Motorcycle 50cc - PassengersKSI12-15 Yrs
4.98​
Motorcycle 50cc - PassengersKSI16 Yrs
6.31​
Motorcycle 50cc - PassengersKSI17 Yrs
7.78​
Motorcycle 50cc - PassengersKSI18-19 Yrs
25.63​
Motorcycle 50cc - PassengersKSI20-24 Yrs
38.25​
Motorcycle 50cc - PassengersKSI25-29 Yrs
27.15​
Motorcycle 50cc - PassengersKSI30-39 Yrs
24.9​
Motorcycle 50cc - PassengersKSI40-49 Yrs
50.5​
Motorcycle 50cc - PassengersKSI50-59 Yrs
27.01​
Motorcycle 50cc - PassengersKSI60-69 Yrs
19.6​
Motorcycle 50cc - PassengersKSI70-79 Yrs
2.34​
Motorcycle 50cc - PassengersKSI80 + Yrs
0.18​
Motorcycle 50cc - PassengersKSIUnknown
4.95​
Car - DriversKilled0-4 Yrs
0​
Car - DriversKilled5-7 Yrs
0​
Car - DriversKilled8-11 Yrs
0​
Car - DriversKilled12-15 Yrs
0​
Car - DriversKilled16 Yrs
0​
Car - DriversKilled17 Yrs
1​
Car - DriversKilled18-19 Yrs
44​
Car - DriversKilled20-24 Yrs
86​
Car - DriversKilled25-29 Yrs
70​
Car - DriversKilled30-39 Yrs
62​
Car - DriversKilled40-49 Yrs
60​
Car - DriversKilled50-59 Yrs
59​
Car - DriversKilled60-69 Yrs
49​
Car - DriversKilled70-79 Yrs
58​
Car - DriversKilled80 + Yrs
59​
Car - DriversKilledUnknown
0​
Car - DriversKSI0-4 Yrs
0​
Car - DriversKSI5-7 Yrs
0​
Car - DriversKSI8-11 Yrs
0.02​
Car - DriversKSI12-15 Yrs
1.24​
Car - DriversKSI16 Yrs
5.43​
Car - DriversKSI17 Yrs
110.14​
Car - DriversKSI18-19 Yrs
449.32​
Car - DriversKSI20-24 Yrs
1108.02​
Car - DriversKSI25-29 Yrs
864.49​
Car - DriversKSI30-39 Yrs
1116.12​
Car - DriversKSI40-49 Yrs
1121.97​
Car - DriversKSI50-59 Yrs
934.3​
Car - DriversKSI60-69 Yrs
665.84​
Car - DriversKSI70-79 Yrs
534.73​
Car - DriversKSI80 + Yrs
434.12​
Car - DriversKSIUnknown
43.4​
Car - PassengersKilled0-4 Yrs
5​
Car - PassengersKilled5-7 Yrs
2​
Car - PassengersKilled8-11 Yrs
2​
Car - PassengersKilled12-15 Yrs
4​
Car - PassengersKilled16 Yrs
11​
Car - PassengersKilled17 Yrs
7​
Car - PassengersKilled18-19 Yrs
27​
Car - PassengersKilled20-24 Yrs
30​
Car - PassengersKilled25-29 Yrs
20​
Car - PassengersKilled30-39 Yrs
27​
Car - PassengersKilled40-49 Yrs
18​
Car - PassengersKilled50-59 Yrs
12​
Car - PassengersKilled60-69 Yrs
10​
Car - PassengersKilled70-79 Yrs
28​
Car - PassengersKilled80 + Yrs
34​
Car - PassengersKilledUnknown
0​
Car - PassengersKSI0-4 Yrs
107.98​
Car - PassengersKSI5-7 Yrs
65.75​
Car - PassengersKSI8-11 Yrs
96.99​
Car - PassengersKSI12-15 Yrs
134.07​
Car - PassengersKSI16 Yrs
79.96​
Car - PassengersKSI17 Yrs
156.6​
Car - PassengersKSI18-19 Yrs
331.74​
Car - PassengersKSI20-24 Yrs
577.16​
Car - PassengersKSI25-29 Yrs
324.27​
Car - PassengersKSI30-39 Yrs
364.81​
Car - PassengersKSI40-49 Yrs
271.94​
Car - PassengersKSI50-59 Yrs
240.23​
Car - PassengersKSI60-69 Yrs
265.49​
Car - PassengersKSI70-79 Yrs
261.8​
Car - PassengersKSI80 + Yrs
210.47​
Car - PassengersKSIUnknown
45.79​
Bus & coach DriversKilled0-4 Yrs
0​
Bus & coach DriversKilled5-7 Yrs
0​
Bus & coach DriversKilled8-11 Yrs
0​
Bus & coach DriversKilled12-15 Yrs
0​
Bus & coach DriversKilled16 Yrs
0​
Bus & coach DriversKilled17 Yrs
0​
Bus & coach DriversKilled18-19 Yrs
0​
Bus & coach DriversKilled20-24 Yrs
0​
Bus & coach DriversKilled25-29 Yrs
0​
Bus & coach DriversKilled30-39 Yrs
0​
Bus & coach DriversKilled40-49 Yrs
0​
Bus & coach DriversKilled50-59 Yrs
1​
Bus & coach DriversKilled60-69 Yrs
1​
Bus & coach DriversKilled70-79 Yrs
0​
Bus & coach DriversKilled80 + Yrs
0​
Bus & coach DriversKilledUnknown
0​
Bus & coach DriversKSI0-4 Yrs
0​
Bus & coach DriversKSI5-7 Yrs
0​
Bus & coach DriversKSI8-11 Yrs
0​
Bus & coach DriversKSI12-15 Yrs
0​
Bus & coach DriversKSI16 Yrs
0​
Bus & coach DriversKSI17 Yrs
0.01​
Bus & coach DriversKSI18-19 Yrs
0.01​
Bus & coach DriversKSI20-24 Yrs
0.13​
Bus & coach DriversKSI25-29 Yrs
2.42​
Bus & coach DriversKSI30-39 Yrs
5.95​
Bus & coach DriversKSI40-49 Yrs
7.43​
Bus & coach DriversKSI50-59 Yrs
7.78​
Bus & coach DriversKSI60-69 Yrs
5.07​
Bus & coach DriversKSI70-79 Yrs
0.06​
Bus & coach DriversKSI80 + Yrs
0.05​
Bus & coach DriversKSIUnknown
0.01​
Bus & coach PassengersKilled0-4 Yrs
0​
Bus & coach PassengersKilled5-7 Yrs
0​
Bus & coach PassengersKilled8-11 Yrs
0​
Bus & coach PassengersKilled12-15 Yrs
0​
Bus & coach PassengersKilled16 Yrs
0​
Bus & coach PassengersKilled17 Yrs
0​
Bus & coach PassengersKilled18-19 Yrs
1​
Bus & coach PassengersKilled20-24 Yrs
0​
Bus & coach PassengersKilled25-29 Yrs
0​
Bus & coach PassengersKilled30-39 Yrs
0​
Bus & coach PassengersKilled40-49 Yrs
1​
Bus & coach PassengersKilled50-59 Yrs
0​
Bus & coach PassengersKilled60-69 Yrs
1​
Bus & coach PassengersKilled70-79 Yrs
1​
Bus & coach PassengersKilled80 + Yrs
4​
Bus & coach PassengersKilledUnknown
0​
Bus & coach PassengersKSI0-4 Yrs
5.06​
Bus & coach PassengersKSI5-7 Yrs
1.62​
Bus & coach PassengersKSI8-11 Yrs
6.14​
Bus & coach PassengersKSI12-15 Yrs
11.53​
Bus & coach PassengersKSI16 Yrs
1.28​
Bus & coach PassengersKSI17 Yrs
2.97​
Bus & coach PassengersKSI18-19 Yrs
5.23​
Bus & coach PassengersKSI20-24 Yrs
16.51​
Bus & coach PassengersKSI25-29 Yrs
15.57​
Bus & coach PassengersKSI30-39 Yrs
30.65​
Bus & coach PassengersKSI40-49 Yrs
24.33​
Bus & coach PassengersKSI50-59 Yrs
41.04​
Bus & coach PassengersKSI60-69 Yrs
58.44​
Bus & coach PassengersKSI70-79 Yrs
82.59​
Bus & coach PassengersKSI80 + Yrs
75.09​
Bus & coach PassengersKSIUnknown
13.72​
Goods vehicle DriversKilled0-4 Yrs
0​
Goods vehicle DriversKilled5-7 Yrs
0​
Goods vehicle DriversKilled8-11 Yrs
0​
Goods vehicle DriversKilled12-15 Yrs
0​
Goods vehicle DriversKilled16 Yrs
0​
Goods vehicle DriversKilled17 Yrs
0​
Goods vehicle DriversKilled18-19 Yrs
1​
Goods vehicle DriversKilled20-24 Yrs
8​
Goods vehicle DriversKilled25-29 Yrs
2​
Goods vehicle DriversKilled30-39 Yrs
6​
Goods vehicle DriversKilled40-49 Yrs
9​
Goods vehicle DriversKilled50-59 Yrs
12​
Goods vehicle DriversKilled60-69 Yrs
6​
Goods vehicle DriversKilled70-79 Yrs
2​
Goods vehicle DriversKilled80 + Yrs
0​
Goods vehicle DriversKilledUnknown
0​
Goods vehicle DriversKSI0-4 Yrs
0​
Goods vehicle DriversKSI5-7 Yrs
0​
Goods vehicle DriversKSI8-11 Yrs
0​
Goods vehicle DriversKSI12-15 Yrs
0.04​
Goods vehicle DriversKSI16 Yrs
0.11​
Goods vehicle DriversKSI17 Yrs
0.29​
Goods vehicle DriversKSI18-19 Yrs
4.8​
Goods vehicle DriversKSI20-24 Yrs
53.12​
Goods vehicle DriversKSI25-29 Yrs
49.23​
Goods vehicle DriversKSI30-39 Yrs
112.8​
Goods vehicle DriversKSI40-49 Yrs
140.35​
Goods vehicle DriversKSI50-59 Yrs
139.42​
Goods vehicle DriversKSI60-69 Yrs
50.69​
Goods vehicle DriversKSI70-79 Yrs
11.99​
Goods vehicle DriversKSI80 + Yrs
2.57​
Goods vehicle DriversKSIUnknown
3.29​
Goods vehicle PassengersKilled0-4 Yrs
0​
Goods vehicle PassengersKilled5-7 Yrs
0​
Goods vehicle PassengersKilled8-11 Yrs
0​
Goods vehicle PassengersKilled12-15 Yrs
1​
Goods vehicle PassengersKilled16 Yrs
0​
Goods vehicle PassengersKilled17 Yrs
1​
Goods vehicle PassengersKilled18-19 Yrs
2​
Goods vehicle PassengersKilled20-24 Yrs
4​
Goods vehicle PassengersKilled25-29 Yrs
1​
Goods vehicle PassengersKilled30-39 Yrs
1​
Goods vehicle PassengersKilled40-49 Yrs
2​
Goods vehicle PassengersKilled50-59 Yrs
0​
Goods vehicle PassengersKilled60-69 Yrs
0​
Goods vehicle PassengersKilled70-79 Yrs
0​
Goods vehicle PassengersKilled80 + Yrs
0​
Goods vehicle PassengersKilledUnknown
0​
Goods vehicle PassengersKSI0-4 Yrs
1.36​
Goods vehicle PassengersKSI5-7 Yrs
1.54​
Goods vehicle PassengersKSI8-11 Yrs
1.22​
Goods vehicle PassengersKSI12-15 Yrs
2.91​
Goods vehicle PassengersKSI16 Yrs
1.17​
Goods vehicle PassengersKSI17 Yrs
1.4​
Goods vehicle PassengersKSI18-19 Yrs
7.86​
Goods vehicle PassengersKSI20-24 Yrs
30.82​
Goods vehicle PassengersKSI25-29 Yrs
13.28​
Goods vehicle PassengersKSI30-39 Yrs
23.42​
Goods vehicle PassengersKSI40-49 Yrs
26.44​
Goods vehicle PassengersKSI50-59 Yrs
14.86​
Goods vehicle PassengersKSI60-69 Yrs
3.67​
Goods vehicle PassengersKSI70-79 Yrs
1.37​
Goods vehicle PassengersKSI80 + Yrs
0.35​
Goods vehicle PassengersKSIUnknown
4.47​
Other vehicle/Unknown vehicle occupantsKilled0-4 Yrs
0​
Other vehicle/Unknown vehicle occupantsKilled5-7 Yrs
0​
Other vehicle/Unknown vehicle occupantsKilled8-11 Yrs
0​
Other vehicle/Unknown vehicle occupantsKilled12-15 Yrs
0​
Other vehicle/Unknown vehicle occupantsKilled16 Yrs
0​
Other vehicle/Unknown vehicle occupantsKilled17 Yrs
0​
Other vehicle/Unknown vehicle occupantsKilled18-19 Yrs
1​
Other vehicle/Unknown vehicle occupantsKilled20-24 Yrs
1​
Other vehicle/Unknown vehicle occupantsKilled25-29 Yrs
1​
Other vehicle/Unknown vehicle occupantsKilled30-39 Yrs
0​
Other vehicle/Unknown vehicle occupantsKilled40-49 Yrs
2​
Other vehicle/Unknown vehicle occupantsKilled50-59 Yrs
5​
Other vehicle/Unknown vehicle occupantsKilled60-69 Yrs
2​
Other vehicle/Unknown vehicle occupantsKilled70-79 Yrs
5​
Other vehicle/Unknown vehicle occupantsKilled80 + Yrs
5​
Other vehicle/Unknown vehicle occupantsKilledUnknown
0​
Other vehicle/Unknown vehicle occupantsKSI0-4 Yrs
1.14​
Other vehicle/Unknown vehicle occupantsKSI5-7 Yrs
0.03​
Other vehicle/Unknown vehicle occupantsKSI8-11 Yrs
1.88​
Other vehicle/Unknown vehicle occupantsKSI12-15 Yrs
5.71​
Other vehicle/Unknown vehicle occupantsKSI16 Yrs
1.19​
Other vehicle/Unknown vehicle occupantsKSI17 Yrs
6.36​
Other vehicle/Unknown vehicle occupantsKSI18-19 Yrs
15.07​
Other vehicle/Unknown vehicle occupantsKSI20-24 Yrs
15.29​
Other vehicle/Unknown vehicle occupantsKSI25-29 Yrs
22.05​
Other vehicle/Unknown vehicle occupantsKSI30-39 Yrs
37.54​
Other vehicle/Unknown vehicle occupantsKSI40-49 Yrs
38.01​
Other vehicle/Unknown vehicle occupantsKSI50-59 Yrs
30.28​
Other vehicle/Unknown vehicle occupantsKSI60-69 Yrs
26.78​
Other vehicle/Unknown vehicle occupantsKSI70-79 Yrs
25.25​
Other vehicle/Unknown vehicle occupantsKSI80 + Yrs
32.1​
Other vehicle/Unknown vehicle occupantsKSIUnknown
2.39​
All road usersKilled0-4 Yrs
10​
All road usersKilled5-7 Yrs
6​
All road usersKilled8-11 Yrs
10​
All road usersKilled12-15 Yrs
22​
All road usersKilled16 Yrs
17​
All road usersKilled17 Yrs
20​
All road usersKilled18-19 Yrs
110​
All road usersKilled20-24 Yrs
225​
All road usersKilled25-29 Yrs
165​
All road usersKilled30-39 Yrs
202​
All road usersKilled40-49 Yrs
254​
All road usersKilled50-59 Yrs
213​
All road usersKilled60-69 Yrs
131​
All road usersKilled70-79 Yrs
155​
All road usersKilled80 + Yrs
173​
All road usersKilledUnknown
0​
All road usersKSI0-4 Yrs
347.76​
All road usersKSI5-7 Yrs
389.63​
All road usersKSI8-11 Yrs
722.99​
All road usersKSI12-15 Yrs
1021.82​
All road usersKSI16 Yrs
454.42​
All road usersKSI17 Yrs
754.6​
All road usersKSI18-19 Yrs
1681.29​
All road usersKSI20-24 Yrs
3686.42​
All road usersKSI25-29 Yrs
2752.31​
All road usersKSI30-39 Yrs
4044.53​
All road usersKSI40-49 Yrs
4359.14​
All road usersKSI50-59 Yrs
3393.01​
All road usersKSI60-69 Yrs
2142.78​
All road usersKSI70-79 Yrs
1556.42​
All road usersKSI80 + Yrs
1274.67​
All road usersKSIUnknown
311.22​

(this is from DataModel)
I see decimals, where is the problem?
 
Last edited:
Upvote 0
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








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 TypeSeverity0-4 Yrs5-7 Yrs8-11 Yrs12-15 Yrs16 Yrs17 Yrs18-19 Yrs20-24 Yrs25-29 Yrs30-39 Yrs40-49 Yrs50-59 Yrs60-69 Yrs70-79 Yrs80 + YrsUnknown
PedestriansKilled5.003.006.0012.004.005.0014.0033.0029.0044.0049.0048.0031.0047.0068.000.00
PedestriansKSI227.76286.25517.00610.25121.96107.56237.17500.14438.78653.73675.00579.03503.17471.39475.2383.10
Pedal cyclistsKilled0.001.001.004.001.002.003.0010.005.0015.0018.0024.0014.008.003.000.00
Pedal cyclistsKSI4.4734.3295.59230.2065.0868.21101.05354.78400.25779.62854.73554.06227.60109.0832.8564.14
Motorcycle 50cc and underKilled0.000.000.000.001.001.000.000.000.001.000.000.000.000.000.000.00
Motorcycle 50cc and underKSI0.000.020.167.93148.1090.5662.8072.5135.6847.8939.4524.1510.743.452.224.68
Motorcycle 50cc - ridersKilled0.000.000.001.000.003.0015.0052.0036.0045.0090.0052.0016.006.000.000.00
Motorcycle 50cc - ridersKSI0.000.000.0012.9623.83202.72440.61919.68559.13847.101108.99800.87305.7052.379.4541.28
Motorcycle 50cc - PassengersKilled0.000.001.000.000.000.002.001.001.001.005.000.001.000.000.000.00
Motorcycle 50cc - PassengersKSI0.000.103.984.986.317.7825.6338.2527.1524.9050.5027.0119.602.340.184.95
Car - DriversKilled0.000.000.000.000.001.0044.0086.0070.0062.0060.0059.0049.0058.0059.000.00
Car - DriversKSI0.000.000.021.245.43110.14449.321108.02864.491116.121121.97934.30665.84534.73434.1243.40
Car - PassengersKilled5.002.002.004.0011.007.0027.0030.0020.0027.0018.0012.0010.0028.0034.000.00
Car - PassengersKSI107.9865.7596.99134.0779.96156.60331.74577.16324.27364.81271.94240.23265.49261.80210.4745.79
Bus & coach DriversKilled0.000.000.000.000.000.000.000.000.000.000.001.001.000.000.000.00
Bus & coach DriversKSI0.000.000.000.000.000.010.010.132.425.957.437.785.070.060.050.01
Bus & coach PassengersKilled0.000.000.000.000.000.001.000.000.000.001.000.001.001.004.000.00
Bus & coach PassengersKSI5.061.626.1411.531.282.975.2316.5115.5730.6524.3341.0458.4482.5975.0913.72
Goods vehicle DriversKilled0.000.000.000.000.000.001.008.002.006.009.0012.006.002.000.000.00
Goods vehicle DriversKSI0.000.000.000.040.110.294.8053.1249.23112.80140.35139.4250.6911.992.573.29
Goods vehicle PassengersKilled0.000.000.001.000.001.002.004.001.001.002.000.000.000.000.000.00
Goods vehicle PassengersKSI1.361.541.222.911.171.407.8630.8213.2823.4226.4414.863.671.370.354.47
Other vehicle/Unknown vehicle occupantsKilled0.000.000.000.000.000.001.001.001.000.002.005.002.005.005.000.00
Other vehicle/Unknown vehicle occupantsKSI1.140.031.885.711.196.3615.0715.2922.0537.5438.0130.2826.7825.2532.102.39
All road usersKilled10.006.0010.0022.0017.0020.00110.00225.00165.00202.00254.00213.00131.00155.00173.000.00
All road usersKSI347.76389.63722.991021.82454.42754.601681.293686.422752.314044.534359.143393.012142.781556.421274.67311.22

<tbody>
</tbody>
 
Upvote 0
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

<tbody>
</tbody>

Keep your data format to DECIMAL
gcCKJvh6tWrDP9lRlM77cGo5QMAGBgAmxOyrmMYvoMAAAAASUVORK5CYII=
 
Upvote 0
add your table directly to Data Model from Power Query without loading any Query Table into the sheet

screenshot-111.png


Don't quote whole post, it doesn't make any sense. Use Reply instead of Reply With Quote
 
Last edited:
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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:
Upvote 0

Forum statistics

Threads
1,212,927
Messages
6,110,718
Members
448,294
Latest member
jmjmjmjmjmjm

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top