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.
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.