Forum

Notifications
Clear all

Messy Data

3 Posts
2 Users
0 Reactions
71 Views
(@mareepiksters-com)
Posts: 19
Eminent Member
Topic starter
 

Hello!

I'm not sure if this is at all possible however, my ERP produces standard reports that I would like my users to continue running and saving in a folder... I would like to collate and use that data in reports (I hope to hand over to them).

However, the standard reports which print to excel with multiple headings and rows seem to have a glitch where partway down the excel extract (usually on page two) column C moves across to Column B... Its weird and annoying but it happens on several of the "standard reports".... So I am racking my brain trying to figure out a way to fix this using power query, so that I can automate the process and have less hands involved... I was thinking new columns if/or statements but I'm not sure

Any advice you could offer would be fabulous.

Thank you

Maree

 
Posted : 22/04/2020 8:51 pm
(@mareepiksters-com)
Posts: 19
Eminent Member
Topic starter
 

I know I have watched a training video where there was an HR file example you had where you needed to cleanse by filling up and another one with nested headings that were on two layers - but I can't find them... could you please remind me which tutorial this was in?

Thank you

Maree

 
Posted : 22/04/2020 11:35 pm
(@mynda)
Posts: 4761
Member Admin
 

Hi Maree,

The columns stepping across sounds tricky! I wonder if you'd be better off duplicating these reports, then filtering out empty rows in the first column of one and in the other you filter out non-empty rows in the first column. Then remove the empty column and append the two queries back together again. Just an idea. It's tricky without seeing the file.

The video you're referring to is the 4.17 Unpivot.

Mynda

 
Posted : 23/04/2020 8:16 am
Share: