Create dynamic links in your tables

Dynamic links are urls including some ID. So by clicking on these links, the user is redirected to any web page and take actions within this page. The security is assured by the website. I strongly recommend to create dynamic links in your tables.

How to

• Open a new report and open Power Query
• Create a new query and paste this M code in the advanced editor :

The query connects to a web page which makes a list of governments open data sources. For this example, we will keep just the list of the countries. You may remove the last step of the query and use the ‘URL’ column to check all the data sources which are available.

let
    Source = Web.Page(Web.Contents("https://fr.wikipedia.org/wiki/Open_data")),
    Data1 = Source{1}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Data1,{{"URL", type text}, {"Entité", type text}, {"Pays/Entité", type text}, {"Date de Lancement", type text}, {"Licence", type text}, {"Données gratuites", type text}, {"Données complètes (non altérées)", type text}, {"Point d'accèsSPARQL & nb triplets", type text}, {"Formats", type text}, {"Thématiques", type text}, {"Note sur l'échelle de Berners-Lee", type text}, {"Column12", type text}}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
    #"Replaced Value" = Table.ReplaceValue(#"Promoted Headers","(",",",Replacer.ReplaceText,{"Pays/Entité"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value",")","",Replacer.ReplaceText,{"Pays/Entité"}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Replaced Value1", "Pays/Entité", Splitter.SplitTextByEachDelimiter({","}, QuoteStyle.Csv, false), {"Pays/Entité.1", "Pays/Entité.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Pays/Entité.1", type text}, {"Pays/Entité.2", type text}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"Pays/Entité.1", "Country"}, {"Pays/Entité.2", "Entity"}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Renamed Columns",{"Country"})
in
    #"Removed Other Columns"

• Open google maps and search any country you got from the query. Let’s use France for this example.
• I got personally this url : https://www.google.com/maps/place/France/
• Just keep the part of the url without the country : https://www.google.com/maps/place/
• Then create a DAX measure like this (replace eventually ; by ,) :

Link = "https://www.google.com/maps/place/" & FIRSTNONBLANK(Countries[Country]; Countries[Country])

• Create a table visual adding the two columns : ‘Country’ and ‘Link’
• Click on ‘Link’ measure then click on ‘Modeling’ tab and in ‘Data category’ select ‘Web url’ :

• Select the table visual and, in the ‘Visualization’ pane, under ‘Values’ check ‘Url icon’

• Then, simply click on the link and let the magic appear
• In case, instead of Google maps, you use your ERP, you may make some modification there. The security will be assured by your ERP. No security issue !
• To create dynamic links in your tables is a very good practice that I recommend strongly !

Here is the pbix file of the demo : Download demo ‘Create dynamic links in your tables.pbix’

For more interactions from your reports, please have a look there : Add data in a table from your reports