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

How to concatinate text with some dynamic value

Hi All,

 

My scenario is, need to show a url hyperlink icon based on the environment. i.e. if the environment is staging, url would have ...staging..., and if the environment is production, url would have ...production...

 

I need to set a custom column having a concatination of some hardcoded text with 'this dynamic server environment'

 

Any way of reading the system properties in powerbi, or reading another database table's value in other table and using that value in a function showing custom column?

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
MarcelBeug Super Contributor
Super Contributor

Re: How to concatinate text with some dynamic value

You can also choose for a Power Query solution (Query Editor).

If you only have a single value, you can also define a Parameter.

 

Example query code with both alternatives:

 

let
    Source = #table(type table[Environment = text],{{"Env 01"},{"Env 02"}}),
    #"Added Custom" = Table.AddColumn(Source, "Environnment and Server from Table B", each [Environment] & " " & Table.FirstValue(#"Table B")),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Environmemnt and Server from parameter", each [Environment] & " " & Server)
in
    #"Added Custom1"

 

Screenshot for parameter definition:

 

Server from parameter.png

Specializing in Power Query Formula Language (M)

View solution in original post

11 REPLIES 11
ashishrj Senior Member
Senior Member

Re: How to concatinate text with some dynamic value

Hi @azizimranz you can create a measure and store your dynamic value into this (with your logic defined). Later you can use this measure to append it with some text data. For instance find below format on how it will goes:

 

Calculated Measure = "Environment is " & [Dynamics_Measure] 

 

Hope this helps !

azizimranz Frequent Visitor
Frequent Visitor

Re: How to concatinate text with some dynamic value

Hi @ashishrj

 

There are two tables I have imported:
Table A: contains all the data
Table B: contains only one row with single column having server name in it.

I have created a measure on Table A, which is getting the server name from Table B
Now when I create a custom column on Table A, I cannot use that measure in it. say like = "Environment is " & [measure_name]


Please let me know where I am wrong.

emadrigal Regular Visitor
Regular Visitor

Re: How to concatinate text with some dynamic value

you can use the function concatenate or simple put your values and use & to join them. 

 

example:

 

measure name = concatenate("hello"," word")

azizimranz Frequent Visitor
Frequent Visitor

Re: How to concatinate text with some dynamic value

Hi @emadrigal

 

Thanks for the reply.

 

I wish you would have read my actual issue.

 

Anyway thanks.

emadrigal Regular Visitor
Regular Visitor

Re: How to concatinate text with some dynamic value

verify that you have relationship many to one and both direction. instead of using a measure you can create the cistom column directly.
Super User
Super User

Re: How to concatinate text with some dynamic value

Hi,

 

Show a sample dataset and the expected result.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
azizimranz Frequent Visitor
Frequent Visitor

Re: How to concatinate text with some dynamic value

Hi,

 

There is no relationship between those two tables.

 

Take one table having all the data, and other table having a single constant value.

 

I want to display the table having all the data, along with the concatination of that constant field in one of the custom columns, (off course for every record).

 

 

 

 

Highlighted
MarcelBeug Super Contributor
Super Contributor

Re: How to concatinate text with some dynamic value

You can also choose for a Power Query solution (Query Editor).

If you only have a single value, you can also define a Parameter.

 

Example query code with both alternatives:

 

let
    Source = #table(type table[Environment = text],{{"Env 01"},{"Env 02"}}),
    #"Added Custom" = Table.AddColumn(Source, "Environnment and Server from Table B", each [Environment] & " " & Table.FirstValue(#"Table B")),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Environmemnt and Server from parameter", each [Environment] & " " & Server)
in
    #"Added Custom1"

 

Screenshot for parameter definition:

 

Server from parameter.png

Specializing in Power Query Formula Language (M)

View solution in original post

azizimranz Frequent Visitor
Frequent Visitor

Re: How to concatinate text with some dynamic value

Thanks a lot @MarcelBeug

 

It works!

 

Here is my implementation:

 

let
    Source = Sql.Database(SQLServer, Database),
    dbo_test_table = Source{[Schema="dbo",Item="test_table"]}[Data],
    #"Added Custom" = Table.AddColumn(dbo_test_table, "Environnment and Server from Table B", each Table.First(#"system_properties")[value]),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Environmemnt and Server from parameter", each Server)
in
    #"Added Custom1"

Helpful resources

Announcements
New Kudos Received Badges Coming

New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (4,139)