Business Objects files and consistency

[Paul_Christie]

I know that whereever it can Power Query will be very helpful and data type columns for me and I'm getting used to checking for the 'gotchas' this can cause. However, I've got a really strange situation. I've got two sets of files produce by Business Objects. Both look identical in Excel with 3 rows of crap at the top and 1 blank column as column 1. With one set of files PQ produces exactly what I would expect and allows me to delete the top three rows and the first column. With the other it still gives me the top three rows and leaves me to handle them but it decides to ignore column 1. It doesn't even put a step in to say that it's done anything with that column, it just completly ignores it.

Has anyone got any ideas what is going on. I'm trying hard to a consistent process that I can hand over to someone less technical than myself and this inconsistency is driving me mad.

 

[Ken Puls]

Hey Paul,

I've never worked with Business Objects but... when you first pull in the data, there will be a gear icon next to the Source step. Maybe try clicking that and forcing the file to load as text? If I'm right that should let you get past the way Power Query has interpreted field delimiters. It should land the data like an un-formatted text file, meaning you'll need to do all the parsing yourself, but the good news is that at least you're in control then.

 

[Paul_Christie]

Ken

Thanks for that suggestion but it didn't work. When I right click on the gear and select text i see a single column of what I'll call print control characters but look like random wingdings.

Paul

 

[Paul_Christie]

Ken

I went back to that source step and started playing around. I still couldn't explain to myself why they were behaving differently so I've looked at the two files with a hex editor and they are quite different in format. The one that is behaving strangely has a block of white space in it. I'm inclined to put this down to there being an 'r' in the month (UK humour) when the file was created. Either that or they were created by different members of the Business Objects team. If there is anyone on the forum who has BO knowledge I'd welcome having a conversation with them.

Paul

 

[Ken Puls]

I'll call someone.

There may still be a couple of tricks to apply here. Again, I don't know BO, so this is a shot in the dark, but you could try:
-Merging all columns before you do anything
-Remove all non-printing characters (right click the column --> Transform --> Clean)
-The separate things

Again, a bit of a shot in the dark, but might help you work around things.

 

[Rick Grantham]

Paul

When you say that you have two files from BusinessObjects, what do you mean by that? Is it a query developed and exported from WebI or Crystal? Or are you connecting directly to the BusinessObjects Universe? Can you provide a little more detail on how you are connecting?

Rick

 

[Paul_Christie]

Rick

I'm doing a project inside a large organisation where the IT infrastructure is outsourced one way and various other bits of the services are outsourced any which way. We don't have direct access to the data, we have to put in a request for a BO query and eventually it gets delivered so we can run it for whichever period we want. I'm using queries that were created pre my involvement so I'm having to live with what I'm given. It's like being treat like a child, my mother used to say 'eat what your given, other children don't even get that'. I can't be sure that someone hasn't messed with the query result before I get to it. The guy who is given me the data has barely got time to do his day job.

Normally the reports we get have a blank column A and 3 header rows that we can delete so that we can promote the fourth row to be column headings. All the files look the same if you simply open them in Excel. But Power Query is seeing two of the files differently. In these 2 files, Power Query ignores completely Column A as though it isn't there. I could probably put them on an area on my OneDrive and let you see them. The data is pretty meaningless unless you have the business code translation table, so there's no real secret in the data.

I'm trying to provide a generic solution that can be run when I'm on my next project. I'm doing a knowledge exchange as best I can but when you get oddities that you can't explain it doesn't help and leaves you looking a bit stupid. I ought to be used to that by now.

Paul

 

[Rick Grantham]

I definitely understand your issue with IT. Data hording sucks

So that I understand. It sounds like you execute a query that BusinessObjects developers created. You log into BO and execute. The tool within BusinessObjects that executes the query is likely called Web Intelligence. And then export to your desktop for PQ. Correct?

And the manner in which you export is no different from one file to the next? Correct?

If so, please share your files with me and I will take a look.

 

[Paul_Christie]

Rick,

I've had a word with my user and this is what he says

I’m not sure I understand the question but if the question is how do we export reports from Business Objects, then:

• We can choose between
  • Excel
  • Excel 2007
  • Pdf
  • CSV

Many of the reports run with pre-set filters, so we always export to Excel 2007. Some reports are ‘split’ across a number of ‘sections’ which are exported as separate worksheets in excel. We tried exporting to csv but the results were unreliable and variable.

The tool within BO may well be called ‘Web Intelligence’….

Hope that helps. I'll post the two files shortly.

 

[Ken Puls]

I'm doing a knowledge exchange as best I can but when you get oddities that you can't explain it doesn't help and leaves you looking a bit stupid. I ought to be used to that by now.

LOL! Not that it helps, but programmers do all kinds of weird stuff. I wouldn't put it past BO to create a malformed file, nor would I put it past PQ to mis-read something on occasion. One thing we can assume is that there is some kind of consistency there. If you can figure out what it is, and it turns out to be an issue on PQ's side, then I can put you in contact with the right person to get it looked it.