Creating a user friendly report from a system report in order to produce a pivot.

[PierremontQuaker03]

Hi, thanks for looking.
I am trying to make some excel files that are user friendly for stock. I run stock by location and I have a fair few locations to run. This file is just one location. The tab "NAV report" is the report that comes out and is a pain to put into a pivot as there are merged cells, blank columns and values on wrong lines, for example row 29, has the right product information, but the numerical information I need is from the row below (Expected cost included line) - this can happen quite a lot in other locations with more product numbers.
Where I have the New Data tab, this is how ideally I would like the information to be displayed - and from this I can easily generate a pivot table, and hide the zeros etc etc.
I believe the best way to do all this would be to create a macro - as I have 15+ location reports to run at month end and painstakingly doing this modification takes a few minutes at a time.
Is it possible for someone to have a look to see how feasible this would be? I know it might be difficult making sure that all the products stay in the right grouping either 2000 or 4000, and no doubt there may be other issues - but I would be grateful for any other comments or thoughts - cheers

 

[Chihiro]

I assume it's from MS Cloud space (D365 NAV), and that you have Office 365 sub.

You can use MS Flow to generate your own report in format needed.

Alternately, you can use PowerQuery to connect to D365.
Or for more control, use App Connector using PowerBI, see link below.
https://docs.microsoft.com/en-ca/dynamics365/business-central/admin-powerbi

While it is possible, it's far more efficient to get data from the source than to transform already processed report.