Como fazer o PROCV no POWER BI (Duas Formas Simples)

Introduction

In this tutorial, we will learn how to use the PROCV (VLOOKUP) function within Power BI, a powerful tool for data analysis and visualization. Unlike Excel, Power BI utilizes different functions and relationships to achieve similar results. We will explore how to use the LOOKUPVALUE function and set up relationships to effectively perform lookups in Power BI.

Step 1: Understanding the LOOKUPVALUE Function

To replicate the functionality of the PROCV from Excel in Power BI, we will use the LOOKUPVALUE function.

Key Points

• LOOKUPVALUE Syntax: The basic syntax is as follows:

  LOOKUPVALUE(<Result_ColumnName>, <Search_ColumnName>, <Search_Value>)
  

• Parameters:
  • Result_ColumnName: The column from which you want to retrieve the value.
  • Search_ColumnName: The column that you want to search in.
  • Search_Value: The value you are looking for.

Practical Advice

• Ensure that the columns you are working with are formatted correctly.
• Use the Data View in Power BI to check your data types and relationships.

Step 2: Setting Up Relationships

Before you can effectively use the LOOKUPVALUE function, it's essential to set up relationships between your tables in Power BI.

Steps to Set Up Relationships

1. Open the Model View: Navigate to the Model view in Power BI.
2. Identify Tables: Locate the tables you want to relate.
3. Create Relationship:
   • Drag a field from one table to the corresponding field in another table.
   • A dialog box will appear. Ensure the relationship is set to the correct cardinality (one-to-many, many-to-one, etc.).
4. Confirm: Click OK to create the relationship.

Practical Advice

• Verify that the data types of the fields you are relating are compatible.
• Use descriptive names for your tables and fields to avoid confusion.

Step 3: Implementing the LOOKUPVALUE Function

Now that you have established relationships, you can use the LOOKUPVALUE function in your data model.

Example Implementation

1. Create a New Column:

   • Go to the Data view and select the table where you want to add the new column.
   • Click on "New Column" in the ribbon.

2. Enter the Formula: Use the following example formula, replacing the placeholders with your actual column names:

   NewColumn = LOOKUPVALUE(Table2[ResultColumn], Table2[SearchColumn], Table1[SearchValue])
   

Common Pitfalls

• Ensure that the Search_Value exists in the Search_Column. If not, the result will return blank.
• Be cautious with case sensitivity and leading/trailing spaces in your data.

Step 4: Visualizing Your Data

After implementing the LOOKUPVALUE function, you can visualize the results in a chart.

Steps to Create a Bar Chart

1. Select the Report View: Click on the Report view icon on the left sidebar.
2. Insert a Bar Chart: Choose the Bar Chart visual from the Visualizations pane.
3. Add Data Fields:
   • Drag the new column (created with LOOKUPVALUE) to the Values section of the chart.
   • Add relevant fields to the Axis section for better context.
4. Customize the Visualization: Adjust colors, labels, and titles as needed.

Practical Tips

• Experiment with different chart types to find the most effective way to display your data.
• Use filters and slicers to allow for interactive data exploration.

Conclusion

In this tutorial, we covered how to use the LOOKUPVALUE function in Power BI to replicate the PROCV function from Excel. We also discussed setting up relationships between tables and visualizing the results in a bar chart.

As you continue to explore Power BI, consider experimenting with other functions and visualizations to enhance your data analysis capabilities. Happy analyzing!