Forum Discussion
M Code to remove blanks and nulls from rows in a single sweep
Here are all the transforms, including the changed types and column names. This replaces the entirety of your function (minus the removal of duplicates, but that's easy to add).
h/t to Mr. von Neumann
I have 56 columns of data. Most of them are regularly populated; some can be blank (e.g. payment date) if the voucher hasn't been processed and matched. So I can accept blanks in some fields and testing for permissible blanks under various circumstances would be a spaghetti plate of if-then logic. In some cases we only know the voucher hasn't paid yet because the payment field is blank. It's only a few fields that if they're blank the record is no good to me.
As an example, PeopleSoft (our ERP) has a known bug that when exporting to .csv if it encounters special characters such as copyright or trademark symbols it interprets that as a line feed/new record indicator. We do our best to clean the data before the extract (and keep our operators from copying product descriptions including those characters into the respective fields), but there are some mandatory fields that if they're blank we know the PeopleSoft extract encountered a problem and we have to throw away the row. (We go in afterward to the source data and figure out what record had the character so we can correct the source.)
In the main body of the voucher query I have the lines to check for those missing fields. But again, if I have to make a change to which columns I want to test I have to edit the query itself and then it runs for hours.
Super UserUse Table.Buffer and Table.AddKey to speed things up.
Well, I'd still have the convoluted decision tree to try and program. I'd much prefer to simply operate on the columns that directly affect whether I keep a row. And a lot of the query load time is spent in creating calculated columns (30) and measures so the PQ portion of is just a portion of the total load. (Since the voucher file is standalone the links to related requisitions, purchase orders, receipts, matches etc., as well as subsequent calculated column computations, have to be done.)
- lbendlin
What would you like to get help with?