Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to Solution.
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
Hope it resolves your issue? Did I answer your question? Mark my post as a solution! Appreciate your Kudos, Press the thumbs up button!! Linkedin Profile |
I'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
Hope it resolves your issue? Did I answer your question? Mark my post as a solution! Appreciate your Kudos, Press the thumbs up button!! Linkedin Profile |
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.
Hi @Xaraja
do you have a list of these letters and their corresponding texts or can you create one?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |