Download the Peppol Directory into Excel - Part 1

Problem

The Peppol directory is a public online directory that allows you to search for Peppol enabled businesses globally. You can search by the name, address or the Peppol ID. In Australia the ID is a Australian Business number (ABN) with the Australian Peppol country identifier (0151) as a prefix. For New Zealand the ID is the NZ Business number (NZBN) with the NZ Peppol country identifier (0088) as the prefix. Full country code lists are available here

Searching the directory is easy but what if you want to do a mass lookup on your supplier or customer master records? There are links to download the entire directory below, but it is the entire global directory and it is a massive file.

Prerequisite

Power Query is an option as it can connect to a web source and you can create a refreshable Excel or Power BI file. It will also allow you to do some processing and filtering.

This article is an introduction to Power Query. The instructions can apply for both Power BI or Excel, but the screenshots below are for Excel.

I also recommend that you turn off the automatically detect column types option in Power Query. Otherwise it tries to guess types and you will get mixed results.

Note that this is a public source so please do not spam the server by refreshing multiple times a second. Be a good user of this public data source.

Solution

1. Find the URL for the for the file you want to download under the Peppol directory. Get the URL for "Participant IDs CSV" which is https://directory.peppol.eu/export/participants-csv. The CSV file will be easier to parse and process but Power Query can process XML and JSON as well.

   

2. In Excel click on the "Data" ribbon and then "From Web".

   

3. In the pop-up, leave it as "Basic" and then paste the URL from above into the "URL field".

   

4. In the next pop-up, it will ask you what credentials you need to connect to the web URL. As no authentication is required, leave it as anonymous and then select "Connect".

   

5. Power Query will try to connect and if successful it will give you a preview of the file in the next pop-up. As you can see the data has a lot of technical id information, but the business numbers are at the end of the file. We will now use Power Query to parse this information and extract the data we require. Select "Transform Data".

   

6. Now we are in the full Power Query editor. Looking at the data we have a single column with a prefix, the Peppol country id and then the business number suffix, e.g. "iso6523-actorid-upis::0088:5798009811554". The data has double quotes " surrounding it, so let's clean this up first. In the "Transform" ribbon, select "Replace values".

   

7. In the pop-up put in " in the "Value to replace". Leave the "Replace With" as blank and then "Ok".

   

8. Since the first line of the file is the column header, let's promote the first row as a header. In the "Home" ribbon, select "Use First Row as Headers".

   

9. Now let's split the data to extract the business number. The data is split by colons so let's use this as a delimiter. Under the "Transform" ribbon, select "Split Column" and then "By Delimiter".

   

10. In the pop-up, choose "Colon" for the "Select or enter delimiter" and then select "Each occurrence of the delimiter". Then choose "Ok".

    

11. Now we have the single column split into four columns, The first two are not really required so we can remove them. Select the first two columns (control click) and then right click and "Remove Columns".

    

12. There are two columns left, the country identifier and the business number. Let's filter down the data to the country we want. Click on the little filter icon, then select "Text Filters" and then "Equals". Enter '0151' for Australia or '0088' for New Zealand or your specific country identifier.

    

13. Now we have the data filtered, one of the last things to do is rename the columns to make it more meaningful. Double click the column heading or right click and "Rename". Use meaningful names such as "Peppol Country Identifier" and "Business Number".

    

14. Finally lets rename our query to make it more meaningful. On the right hand side, under "Query Settings" and "Properties" change the "Name" to something meaningful.

    

15. All done and now lets load the data into Excel. Under the "Home" ribbon select "Close & Load".

    

The full code is provided below. Remember to replace the [Replace with country identifier] 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] = "[Replace with country identifier]"),
    #"Renamed Columns" = Table.RenameColumns(#"Filtered Rows",{{"Participant ID.3", "Peppol Country Identifier"}, {"Participant ID.4", "Business Number"}})
in
    #"Renamed Columns"

Troubleshooting

If you ignored the prerequisite instructions about disabling the automatic type checking your data may look a little funny. In the right hand side under "Applied Steps", delete any steps with "Changed Type" and it should hopefully work.