Download the Peppol Directory into Excel - Part 2

Problem

The first part of this series demonstrated the process of creating an updatable Excel file that allows you to access a list of Peppol enabled businesses . However, in that article, the country identifier was hardcoded, which required code or step modifications for any changes. Ideally, this should be a user entered variable, enabling dynamic changes to the country identifier and the data being filtered.

This article assumes limited familiarity with Power Query. The instructions can apply for both Power BI or Excel, but the screenshots below are for Excel.

Solution

This is quite straightforward in Power Query, as it has the ability to create parameters or variables.

1. Open the Power Query editor. In Excel, the quickest way it is under the "Data" ribbon, "Get Data" and "Launch Power Query Editor".

2. In the Power Query Editor, under the "Home" ribbon, "Manage Parameters" and then "New Parameter".

   

3. Create a new Parameter. Under "Name" enter a meaningful name like "Country Identifier". Enter a meaningful "Description". Important, remember the name of this as you will need it later.

4. Tick "Required" and change the "Type" to "Text". For the more advanced users you can change the "Suggested Values" to "List of values" and populate a list that can be selected via a dropdown. For the purposes of this article leave it as "Any value" so the user will type it in manually. As you have made this required, you will need to enter something in the "Current Value". So enter '0151' or the appropriate identifier for your country.

   

5. Switch to the query that you want to modify from part 1, e.g. "Australian Peppol Identifiers". On the right hand side find the step for "Filtered Rows". Click on the cog wheel.

   

6. In the pop-up, change the "Text" (abc) dropdown to "Parameter". Select the parameter that you created in step 3.

   

And you are done! You can change the parameter now to a different identifier and refresh to get a different set of values.

The full code is provided below. Remember to put in your parameter in line 7. You can copy and paste it into the advanced editor.

let
    Source = Table.FromColumns({Lines.FromBinary(Web.Contents("https://directory.peppol.eu/export/participants-csv"), null, null, 65001)}),
    #"Replaced Value" = Table.ReplaceValue(Source,"""","",Replacer.ReplaceText,{"Column1"}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Replaced Value", [PromoteAllScalars=true]),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Promoted Headers", "Participant ID", Splitter.SplitTextByDelimiter(":", QuoteStyle.Csv), {"Participant ID.1", "Participant ID.2", "Participant ID.3", "Participant ID.4"}),
    #"Removed Columns" = Table.RemoveColumns(#"Split Column by Delimiter",{"Participant ID.1", "Participant ID.2"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Columns", each [Participant ID.3] = #"Country Identifier"),
    #"Renamed Columns" = Table.RenameColumns(#"Filtered Rows",{{"Participant ID.3", "Peppol Country Identifier"}, {"Participant ID.4", "Business Number"}})
in
    #"Renamed Columns"