Nasty little data type problem

jmlee

New member
Joined
Apr 28, 2020
Messages
8
Reaction score
0
Points
0
Location
Bonn, Germany
Excel Version(s)
O365
Greetings,

I have built a rather involved set of power queries to handle monthly billing data for a customer. In final processing, I ran into a tricky data type problem that forced me to half rebuild part of my model. My question is: how can I force excel (not PQ) to treat a mixed data type column (numerical & text) always as text?

Here's the context to the question...

The set-up:
We provide IT services that are grouped into Business Services for billing. Each month, the quantities are reported from the infrastructure (server names with number of CPUs & RAM, amounts of storage or backup, etc.). The billing model allocates the reported data per the contractual terms to the Business Services, which are further charged to the customer. As different sizes and quality (gold, silver, bronze) come into play, it gets quite involved. The "logic" of the model is in the queries and a set of master tables. By passing the monthly reports through the model, we get properly billable data.

The problem:
The ID number for the Business Services, "BSID", is a mixed data type. Until this year, the ID was numerical (8 digits). Due to a tool switch, the new ones are text ("B"+7-digit number). The BSID is stored in a master table. The report files are .xlsx, exported from database tools (outside my control), and the BSID appears to come out as a mixed format.

Until yesterday, my queries were handling the mix with data type "any" without issues. Suddenly, my query started only matching the BSIDs with a "B" in them, i.e. the text ones. As a workaround, I coerced the BSID during query processing to text by appending an additional "B", which i then removed at the end (the data goes to other departments who need the original BSIDs). When I put the results into a final table that uses VLOOKUPs, some (but not all) of the purely numerical BSIDs generated #N/V errors. Entering the cell and hitting return solved the problem, because excel then regarded them as numbers again.

My question:
Is there a way to force the master table to handle the ID as text? (Just changing it to "text" via the formatting dropdown in excel didn't work.)

This appears the best solution, as I can then force a text data type for the reported data within PQ. And the final result can also be generated as text.

The solution needs to be robust, i.e. allow entry of new or changed IDs by others with minor excel knowledge.

Thanks for any advice! (Please note, as this deals with financial data, I cannot upload an example.)

Cheers,
Jamie

P.S. I realize that in the end this is a regular excel question, not specifically a PQ question. But, the interaction with PQ plays a role.
 
All of this will be much easier to help with once you attach a sample workbook. :)
 
Maybe you could try sorting the BSID descending in Excel before loading into Power Query, as Power Query will look at the first n rows to determine the data type.

Or maybe add a column in EXcel with a formula like [FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]=IF(ISNUMBER(A2),"'"&A2,A2).[/FONT]
 
Thanks, Bob. Both ideas may work. I'll give them a try tomorrow. I knew that apostrophes maintained "as entered", but I was unaware that they coerced to text. The problem may not be with my master data, but with the reported data. My hope was to just drop the raw files into a folder with no processing.

@ Ali: Yeah, I thought about anonymizing, but the data is so involved…. Let me see if Bob's suggestions will work. If not, I'll post something.

What was weird about my case, is that I have two main queries: one for processing storage & backup data, and one for processing most everything else. They are separate because of their source data and because storage & backup requires complications that the rest does not. S&B was working great. Then I completed the other query. As I went back to S&B, it was broken (as above). But, both of them rely on the same master tables for the BSID etc.

Another thing that I noticed: the PQ-Interface showed a subtle difference in the BSID column. The "B"+number entries were shown normally (non-italics), as were some of the 8-digit entries. But, a whole set of the 8-digit entries appeared in italics.
 
Your solution might be the issue i'm facing where my mixed data type identifer needs to be interpreted as both text and numeric still but you just need them all defaulted to text only even if its for pure numeric value?

If so - try apply a simple trimming step to your identifer field and the outcome - is a forced text data type vs changing data type as {"ID" , type any}

Hope this helps abit
 
Back
Top