cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
nblair Frequent Visitor
Frequent Visitor

Add Column or transform?

I have a dataset with 15 columns of data, each column is the same type of data in integer form.   These integer represent a text value.   When I pull the data down from the cloud, what is the best way to convert it to the text form of it?  Charts need to show the text.. not the integer to make sense.   I have a table of the text values with the integer equiv but it seems inefficient to create a query with 15 copies of the table linked into the individual columns... 

 

As I'm new to Power BI.. I'm wondering if there is a way to do it that is better.

 

thanks

 

5 REPLIES 5
Super User
Super User

Re: Add Column or transform?

see integer value is the best option. 

 

all the visuals only play with measures so integer is best one dude

nblair Frequent Visitor
Frequent Visitor

Re: Add Column or transform?

not sure if I just don't know the terminology or if I didn't explain well enough...   

 

Table might have an integer 0,1,2,3,4,6,9,13,24, or about 15 other values...

 

0 = Off

1 = Text 1

2 = Text 2

3 = Etc Etc

 

I want the charts to display using the text values...   

 

So if I have a stacked bar for column 3... it would break it down and display the text.. not the integer.

KieranHarmon Frequent Visitor
Frequent Visitor

Re: Add Column or transform?

If creating the relationship is not efficient enough for you then you can do a transformation though this may not be more efficient, you will have to judge for yourself.

 

1. Click Edit Query

2. For each column, change its Date Type to text

3. For each column, you will now have to do a Replace Values ( see "Replace Values" under the Transform section. ). E.g. in the Replace Values dialogbox, you will enter 1 in the Value to Find, and enter "Text1" in the replace With value. You SHOULD select Match entire cell contents under Advanced Options so that any other cell that contains a 1 in a number isnt affected e.g. you wouldnt want the number 11 being replaced with "Text1Text1" Smiley Happy

 

 

 

 

nblair Frequent Visitor
Frequent Visitor

Re: Add Column or transform?

So I would guess then that linking up tables is the best way because there will be 200k+ records pulled down at a time.

 

I just thought there might be a better way.  I wanted to keep the load on the network light so that is why I wanted to keep it in integer form for storage and retrieval.  

 

thanks for the help.

KieranHarmon Frequent Visitor
Frequent Visitor

Re: Add Column or transform?

Both solutions work on the data after it is retrieved from the data source, whether it is the relationship to a lookup table in PowerBI or the transformation option. Both happen in Power BI and so there is no network performance difference between them. Your idea of only pulling the integer and not the text is still valid.