05.1- Use Power Query to automate Power BI and Excel reports with PRIMAVERA XER file – XER reader.

Unlock the power of XER Prim Vera files with Power Query, no third party tools needed! Say goodbye to hours of manual data manipulation and hello to automated reports and dashboards. Just follow these steps, change parameter values as you go, and watch the magic happen. Now you’re on your way to unlocking a whole new level of data analysis and visualization with Power BI 😎📊 #DataAutomation

Overview 📊

Are you tired of spending hours copying and pasting Prim Vera data into Excel to prepare for Power BI reports and dashboards? This video will teach you how to automate this entire process using Power Query to unlock XER Prim Vera file data. No third-party tools needed – let’s simplify your workflow and save time!

Understanding XER Files

To start, understand that the XCR file ONLY needs to be read and explored – it’s the easiest part of the process. Two ways to access the file path and name are provided:

1. Select the file ➡️ right-click to copy the path.
2. Right-click properties ➡️ under security, copy the path directly from the object name box.

Using Parameters in Power Query 💻

Parameters are key in automating your queries. Here’s how to do it:

1. Open a new PowerP file and access Power Query from the home tab.
2. Add a new parameter, change the parameter name to XC path, and paste the XCR file path in the current value field.
3. This creates a variable that stores a value to be reused in your queries. The potential of this feature is about to shape your automation game.

Automating the Process

Start by creating a temporary query to understand the methods manually and then automate the process later:

Reading XCR File Contents 📑

Once you’ve set up the parameters, you’re ready to dig into the XCR file content and pull it into Power Query. Here are the steps:

1. Use the "file.docontent" function to read the file content through the file path (XTR path).
2. The "CSV.Document" function will divide the file into an adjustable number of columns, and "Encoding" should be set to 1252 to capture currency symbols correctly.

Creating Tables from XCR Data 📈

The XCR data is divided into tables, and we’ll explain how to find, use, and divide them into distinctive ranges:

Creating the Task Table

1. Understanding the arrangement of the records.
2. Removing unnecessary rows.
3. Replacing null values.
4. Adding the table name column.
5. Filtering records.

Conclusion ✅

The potential of automating with PRIMAVERA XER files and Power Query is undeniable. If leveraged correctly, this process can transform your workflow and significantly improve efficiency.

Key Takeaways

• Automating with Power Query simplifies the data preparation process.
• XER files can be easily manipulated to suit your reporting needs.

FAQ 🤔

Q: Can these methods be used for larger datasets?
A: Yes, these methods are scalable and can handle large datasets efficiently.

Remember, the possibilities are endless, and automation is the way forward. Happy automating! 🚀