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:

     

Ashish_Mathur's avatar
Ashish_Mathur
Super User
8 years ago

Hi,

 

Show a sample dataset and the expected result.

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