Table of Contents
The goal of this function is to allow all the programme data to be imported directly from an XER file, no need for excel or CSV conversions. By creating a function inside PowerQuery we have enabled teams to pull in multiple XER either locally or via an integration. As this is a function rather than a custom connector it also supports the cloud version of PowerBI, meaning dashboards can be automatically updated as and when new programmes arrive.
This is one example of how to get up and running, there are plenty of different way you can use the function, or even adapt it for your own requirements.
Before you get to work you will need to have a basic understanding of the PowerBI or PowerQUery in Excel toolset and a copy of either one installed
- Open up the XER4PowerBI.pq file in notepad or your text editor of choice
- In PowerBI or Excel create a blank query (in PowerBI it's Home > Get Data > Blank Query, in Excel Data > Get Data > From Other Sources > Blank Query
- Right-click the Query in the query editor (often called "Query1" in new documents) and click Advanced Editor
- Paste the contents of XERPowerBI.pq into the Advanced Editor and click done
- You now have a function for opening XER Binary files in PowerQuery, now you need to find your binaries
- Rename the function by right-clicking it, the name of the function will be how we reference it later, so it's best to use XER4PowerBI
- The easiest way to do this is to locate a folder with one or many XER files. New Source > Folder
- Select your folder and click open. Use the PowerBI filters to filter out any files that are not required
- Create a new column Add Column > Custom Column
- In the formula box type XER4PowerBI([Content]) . This assumes you named the function XER4PowerBI and you now have a column called Content which contains Binary values
- This will leave you with a new column consisting of a table, which you can expand to find all of the tables from your XER file. Make sure you delete the Content column before expanding to avoid exponential memory consumption
- Add pretty names for column headers
- Look for efficiencies
Contributions are what make the open source community such an amazing place to learn, inspire, and create. Any contributions you make are greatly appreciated.
If you have a suggestion that would make this better, please fork the repo and create a pull request. You can also simply open an issue with the tag "enhancement". Don't forget to give the project a star! Thanks again!
- Fork the Project
- Create your Feature Branch (
git checkout -b feature/AmazingFeature) - Commit your Changes (
git commit -m 'Add some AmazingFeature') - Push to the Branch (
git push origin feature/AmazingFeature) - Open a Pull Request
Distributed under the GPL 3.0 License. See LICENSE.txt for more information.
Matthew Osment - LinkeIn - matthew@shift-construction.com