Rateio no Power Query [ Distribuição Proporcional ]

Summary

In this educational video, the host, Juviano Silveira, introduces the concept of proportional distribution in Power Query, using a relatable example of sharing bananas among family members. He then transitions to demonstrate how Power Query can be utilized to distribute sales data based on cubic meterage, guiding viewers through the process of linking tables, calculating, and applying direct distribution. The tutorial is interactive, encouraging viewers to engage with the material and apply the concepts to their own data analysis.

Takeaways

• 📚 The video is a tutorial on how to perform proportional distribution in Power Query, a feature of Microsoft Excel.
• 🍌 The instructor, Juviano Silveira, uses the analogy of distributing bananas among family members to explain the concept of distribution.
• 📈 The tutorial covers both direct distribution and cascading distribution, comparing them to the simple example of sharing bananas.
• 🔗 The importance of understanding Power Query and loops is emphasized for advanced Excel users to master the distribution process.
• 📋 The video includes a step-by-step guide on how to set up tables in Power Query, using references to link data from different tables.
• 🔢 The concept of 'M3 Total' or total cubic meters is introduced as a basis for the distribution of values, like the size of the bananas to be shared.
• 🚚 The tutorial demonstrates how to link freight expenses to sales data based on the cubic meters of products sold.
• 🔍 The process of filtering and searching for specific data within Power Query tables is shown, using the example of finding freight expenses.
• 📊 The instructor guides viewers on how to aggregate data, such as total cubic meters, to prepare for the distribution calculation.
• ⚖️ The final step involves calculating the distributed freight cost based on the total cubic meters and the number of freight documents (CTE).
• 💡 The video concludes with a challenge for viewers to download the provided spreadsheet and replicate the distribution process to better understand the technique.

Q & A

What is the main topic of the video?

The main topic of the video is teaching how to perform proportional distribution, specifically in Power Carry, using a practical example of distributing bananas among family members.

Who is the instructor in the video?

The instructor in the video is Juviano Silveira.

What is the example used to explain the concept of distribution?

The example used is buying a bunch of bananas and distributing them among family members, including a pregnant wife, to illustrate the concept of proportional distribution.

What are the two types of distribution mentioned in the video?

The two types of distribution mentioned are 'rateio em Ramos' (cascading distribution) and 'rateio direto' (direct distribution).

What is the focus of the lesson in the video?

The focus of the lesson is on 'rateio direto' or direct distribution, which is a method of distributing values based on a single level of hierarchy.

What is the purpose of creating a 'vendas' table in Power Carry?

The purpose of creating a 'vendas' table in Power Carry is to organize and reference data from the sales sub-table in the source spreadsheet for the distribution calculation.

How does the instructor plan to incorporate the freight cost into the distribution?

The instructor plans to incorporate the freight cost by linking it with the sales data based on the freight number, which is connected to the sales invoice.

What is the significance of the 'M3 Total' column in the video?

The 'M3 Total' column represents the total cubic meterage, which is used as the basis for the proportional distribution calculation.

How does the instructor demonstrate the calculation of the distributed freight cost?

The instructor demonstrates the calculation by dividing the total cubic meterage by the number of CTE (freight documents) and then multiplying by the freight cost to get the distributed cost per CTE.

What is the final step the instructor performs in the video?

The final step is to filter and calculate the distributed freight cost for each CTE, showing the results in a 'vendas frete rateado' (distributed sales freight) table.

What challenge does the instructor pose to the viewers at the end of the video?

The instructor challenges the viewers to download the spreadsheet and try to replicate the distribution process to better understand and learn how direct distribution works.