Question on Replace Function

[tksnota]

Hi,

I add a new column by copying an existing one and changing the format from numbers to text (copied year column and division new one) then add my text as shown so that i can create a relationship with another sheet.

I'm wondering if I refresh the original source data and new rows are introduced...will power bi automatically add the text that i entered in the new column.

//NT

[tksnota]

my requirement is establish a relation with another sheet so by adding a new column without using custom just by copying any existing one and changing the field from number to text

the above requirement is satisfied. but my question since i haven't used custom column only copy an existing one and made the changes...will the new column automatically introduced the changes i maded in this case a text after a refresh and numerous rows are created

hope i make myself clear

[bhanu_gautam]

Select the column you want to copy.
Right-click on the column header and select Duplicate Column. This will create a new column with the same values as the original.

Select OrderID_Text.
Go to the Transform tab.
Click on Data Type and select Text

When steps are recorded in Query editor than new data will also be updated you can follow this method

[tksnota]

thanks. I had done this exercise already...

[bhanu_gautam]

Can you explain what is your exact requirement as compared to your original column

[tksnota]

Hi,

 Will the column add the text when new rows are introduced after refresh because I got conflicting answer?

I attempted to use Custom Column but I don't know how to do it. Can someone share me the syntax i need to use?

[shafiz_p]

@tksnota Please find the image below to create custom column. If your column is integer data type, then you need to convert it to text first to add custom text, otherwise it will throw error.

Number.ToText() function helps to convert numbert to text and '&' is used as concat text.

If this solves your problem, mark it as solution.

[tksnota]

Hi,

Your code is great. Is it possible to capture the Text without the year as shown?

//NT

[shafiz_p]

@tksnota Based on my understanding, you’d like to concatenate the ‘Division’ column with the ‘Year’ column and change the data type to text.

To achieve this, simply replace the hardcoded value ‘IND’ with the actual column name ‘[Division]’ and update the data type accordingly.”

Feel free to ask if you need further clarification or assistance!

If this answered your question, please mark it as solution!!

[tksnota]

Thanks. Is it possible to do the following

1. concatenate Division with Invoice amount (Relationship 01)

1. concatenate Invoice Date  with comments (Relationship 02)

[shafiz_p]

@tksnota Yes it is possible.

1. Division with invoice amount - [Division] & Number.ToText([Invoice amount])
2. Invoice Date with comments - Date.ToText([Invoice Date]) & [Comments]

Hope this answered you question. If, please mark it as a solution!!

[tksnota]

this is great...how can i add comma to distinguish between the 2 data

[shafiz_p · Accepted solution]

@tksnota Put comma inside a double quotation and use & like below:

Date.ToText([Invoice Date]) & " , " & [Comments]

Hope this answered your question. If, please mark it as a solution!!

[shafiz_p]

@tksnota As I can understand, that you want to create a key column to develop relationship with other tables. Yes, If you create a column based on other columns, as you mentioned, and add specific text, after refresh, the new column will automatically update whenever new data is added to the source file.

If this answered you question, please mark it as solution.

[bhanu_gautam]

@tksnota , In Power BI, when you create a new column by copying an existing one and then manually add text to it, the new column will not automatically update with the text you entered if new rows are introduced upon refreshing the original source data. This is because the manual text entries are not part of the data transformation logic and are not dynamically applied to new data.

To handle this you can use Custom column and try to all modifications on the basis of that