Forum Discussion

azizimranz's avatar
azizimranz
Frequent Visitor
8 years ago
Solved

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 wou...
Need Help
Show and Tell
  • MarcelBeug's avatar
    MarcelBeug
    8 years ago

    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:

     

azizimranz's avatar
azizimranz
Frequent Visitor
8 years ago

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).

 

 

 

 

MarcelBeug's avatar
MarcelBeug
Community Champion
8 years ago

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:

 

  • azizimranz's avatar
    azizimranz
    Frequent Visitor
    8 years ago

    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"

    • MarcelBeug's avatar
      MarcelBeug
      Community Champion
      8 years ago

      It looks you implemented both alternatives instead of chosing one of the 2 (either a parameter or a table with 1 value).

      • azizimranz's avatar
        azizimranz
        Frequent Visitor
        8 years ago

        Hi MarcelBeug

         

        Yup I know, its just a testing work. Works well. Thanks

         

        There is one another issue, if you can help me out in it please. See this