Ken has attended:
Excel Forecasting and Data Analysis course
PowerPoint Intermediate Advanced course
Error message trying to cast numerical column from PowerQuery/Po
I'm trying to build a pivot table in excel pulling live data from Business Central. The only means I can find to do so is to create and publish a PowerBI report from BC data, and then create a data connection from PowerBI into Excel.
Unfortunately, when attempting to create a pivot table from that data, when pulling a numerical column into the "Σ Values" PivotTable Field, I am met with the following error message:
"The field you are moving cannot be placed in that section of the report"
I have checked in PowerQuery and that column of data is 100% valid, and numerical (have tried both as decimal and as currency), so I am stumped why it won't let me cast the data.
Any help or pointers (or walkthrough of the correct way to create a connection from Business Central financial data into Excel) would be much appreciated!
RE: Error message trying to cast numerical column from PowerQuer
Hi Ken,
Thank you for your question to the forum
In order to connect Business Central directly to Excel via Power Query you can follow the link below:
https://www.youtube.com/watch?v=EIzGX0reSFI
If you get the error message you mentioned, you will need to create a measure in Power Pivot available within Excel and output it to a Pivot table. To enable the Power Pivot tab on the Excel ribbon go to FILE > OPTIONS > ADD-INS. In the MANAGE field, select COM Add-ins, click on GO and tick for Power Pivot in the list.
To create a measure in Power Pivot you can follow the link below:
https://www.coursera.org/lecture/excel-power-tools/introduction-to-dax-measures-fNSM8?utm_medium=sem& ;utm_source=gg&utm_campaign=b2c_emea_x_multi_ftcof_career-academy_cx_dr_bau_gg_pmax_gc_s1_en_m_hyb_23-12_x&campaignid=20858198824&adgroupid=&device=c&keyword=&matchtype=&network=x&devicemodel=&creativeid=&assetgroupid=6484888893&targetid=&extensionid=&placement=&gad_source=2&gclid=EAIaIQobChMIxZ6Ywoq8iwMVhouDBx0Z5jMfEAEYASAAEgJOEPD_BwE
I hope this has helped you and please let us know if it has worked on your data
Kind regards
Martin Sutherland
(IT Trainer)
