Forum Discussion
Helper IIIBest Method to Transform Codes to Meaningful Data
I am working with data that tends to have a lot single or double letter codes for which the meaning is not always obvious and which need to be transformed into what they mean before they are displayed in the reports that are built from the data. I understand that I could do this in a variety of ways but I want to make sure I do it in the most effective way both for dataset refresh and for report performance. For example, I am looking at a column for "record type" that has the potential list of codes:
C = Customer
S = Ship to
FC = Forecast Customer
FS = Forecast Ship to
FG = Forecast Group
In Power Query I could use a replace text function to replace the letters (i.e., replace FC with FC-Forecast Customer), or I could add a column based on the values in this column, which is what I have seen a lot in the existing datasets that I'm trying to clean up (which seem to run very slowly). I could also do this in DAX instead of Power Query, I believe. I do not currently have the option to make these changes in the data source, although I understand that would be desirable if it became available as an option.
I tried searching and got articles about how to go about creating a conditional column which wasn't helpful. Are there any suggestions for the pros and cons involved or the best overall options or just some suggested reading?
Hi Xaraja
For every column with codes, your best bet will be to create a separate DIM table.
If you are not able to chage the dataset, then there may not be much that can be done.
Regards,
Pranit
4 Replies
XarajaI'm creating the lists as I come across them. I have to research what the letters are and what they correspond to as I come across each column in each table that has such a list. There are many such columns with independent lists of what the letters mean and unfortunately the letters are reused. So for example, in a Status column, I=Inactive, and in a Type column, it might be I=Inventory Control.
Hi Xaraja
For every column with codes, your best bet will be to create a separate DIM table.
If you are not able to chage the dataset, then there may not be much that can be done.
Regards,
Pranit
- Xaraja
A separate table in the dataset? Like I would create a tiny table in my dataset with two columns and five rows with the data from my original post? And then when building the report use the labels from that table instead of ever pulling them in as a column?
I can build the datasets however I want. What I cannot do currently is replace all these single and double letter codes in the original data source with the text that they stand for. That is, I cannot back up to before the data is pulled into Power BI and get rid of the problem entirely.
