The first step is, of course, to click in the table and go to Power Query –> From Table. So that’s what I’m going to start doing now. I really need to break this into separate pieces, and make useful and readable columns out of it. So great, we’ve got this awesome billing code schema, but it doesn’t really tell me anything when I look at it, as it’s too complicated to read. So a table of customer data could look like this: Char 10 – Text – Indicates the payment method (F = Financed, P = Paid up front, C = Comp/Honorary).Char 9 – Text – A variety of single letter codes indicating specific things we want to know.Char 7-8 – Variable – Slip length (in feet) for a boat in the case of marina customers, or SG, CP or CS for golf (indicating single, couple primary or couple spouse). Char 5-6 – Numeric - Indicates the start month (and subsequent anniversary) for the customer’s product.Char 3-4 – Numeric – Indicates the number of months of coverage for the product (1-12).Char 2 – Alpha – Indicates the billing type (D = Dues, P = Pass, A = Annual Moorage, P = Periodic Moorage).Char 1 – Alpha – Indicates the division (G = Golf, F = Fitness, M = Marina).It’s always 10 characters long, and breaks down like this: There’s no real mystery to the first two items, but the billing code schema we designed holds a ton of info. While the data has been scrambled, this represents a real structure that we use in my day job. The base scenarioįor this example I’m going to work with a table of data that holds a customer number, a boat type and a billing code schema. That may strike you as weird in this one, but we need to remember that “if” is not the same as “IF”, and that Power Query will gag on the latter. The only important thing we need to remember here is that all functions in Power Query, whether text, logic or anything else, are case sensitive. Today we’re going to look at another compare/contrast scenario, but this time it’s going to be the IF function in Power Query. In my last post I talked about useful text functions, and how they differed between Excel and Power Query.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |