Hello! Welcome to the first lesson in our new module, "Building Interactive Reports in Power BI."
In all our previous lessons, we've been working "behind the scenes" in the Power Query Editor. You've learned how to import, clean, transform, and reshape data—culminating in our last lesson on using Unpivot to turn wide, unfriendly tables into a perfect format for analysis. You are now fully equipped to teach the data preparation stage of a project.
Today, we make the exciting transition from data preparation to data visualization. This lesson focuses on a simple but critical step that bridges those two worlds. Our learning outcome is to import cleaned data from Power Query into Power BI. We'll cover the process of finalizing your work in Power Query and loading the resulting clean data into the main Power BI Desktop environment, making it ready for building reports. For the managers in your workshop, this is the satisfying moment when their data cleaning efforts culminate in a usable dataset.
The Two Worlds: Power Query and Power BI
Before we perform the action, it's crucial to understand the conceptual separation between the two environments you've been using. This is a key point to clarify for your workshop attendees.
- Power Query Editor: Think of this as the "data kitchen." It's where you import raw ingredients (data), clean them, chop them up, combine them, and prepare them according to a recipe (your applied steps). Its background language is M.
- Power BI Desktop: This is the "report canvas" or the "dining room." It's where you take the perfectly prepared data from the kitchen and arrange it into beautiful, insightful visualizations for your audience. Its background language for calculations is DAX.
Power Query's job is to push the prepared data into Power BI. This workflow ensures that your analysis and visualizations are always built on a clean, reliable foundation.
The 'Power BI for Beginners' guide provides a concise explanation of this distinction. Understanding the roles of M and DAX will help you answer questions about why certain tasks are done in one place versus the other.
Read the section titled 'DAX vs. M vs. GUI (BN)' (you can find it by searching for that title). Focus on the explanations of 'What is M?' and how Power Query 'pushes' the data into Power BI. This conceptual understanding is vital for you as the instructor.
The Bridge: From Power Query to Power BI
The entire process of moving data from the Power Query "kitchen" to the Power BI "canvas" is handled by a single command: Close & Apply.
Let's imagine you've just finished all the cleaning steps from the previous modules. Your data is structured, data types are correct, and unnecessary rows/columns are gone. You are now ready to load it.
When you click Close & Apply, Power BI does two things:
- Close: It closes the Power Query Editor window.
- Apply: It applies all the steps you defined in your query to the source data and loads the final, transformed table(s) into the Power BI "Data Model."
This video provides a perfect, concise demonstration of the entire workflow, from opening Power Query to closing it and seeing the result.
How to use Power Query in Power BI | Microsoft Power BI for Beginners
The video 'How to use Power Query in Power BI' by Alex The Analyst will walk us through the complete sequence. We'll see data being brought into Power Query, transformed, and then loaded into Power BI.
First, watch the segment from 00:00 to 02:30. This serves as a quick recap, showing how data is imported and the 'Transform Data' button is selected to open the Power Query Editor. Then, skip to the end and watch from 12:10 to 13:08. This is the core of today's lesson. It shows the 'Close & Apply' step and what happens immediately after in Power BI Desktop.
Where to Find Your Data in Power BI
After you've clicked "Close & Apply," the loading process might take a few moments. Once it's done, your data is available within the main Power BI Desktop window. Here’s where to find it:
- The Fields Pane: On the right side of the screen, the Fields pane will now list all the tables and columns you loaded. This is your palette for building visualizations. You'll drag fields from here onto the report canvas.
- The Data View: On the left-hand side, there are three icons. The middle one, which looks like a table, is the Data view. Clicking this allows you to see the actual data in your tables, just like in an Excel spreadsheet. This is great for a final inspection.
- The Model View: The bottom icon on the left is the Model view. This is where you can see relationships between your tables. We'll touch on this more in later lessons.
The following video shows this process again and gives a clear view of where the data appears in the interface.
Power BI Tutorial For Beginners | Create Your First Dashboard Now (Practice Files included)
Let's watch a section from Leila Gharani's 'Power BI Tutorial For Beginners'. She provides another clear example of the 'Close & Apply' process and then explores the different views in Power BI where the data can be found.
Watch from 08:24 to 10:41. Pay attention to how she clicks 'Close and apply', explains the loading process, and then points out the loaded tables in the 'Fields' pane, the 'Data' tab, and the 'Model' view.
Test your understanding!
A manager in your workshop has just followed your instructions to clean a sales dataset in Power Query and has clicked "Close & Apply". They now want to build their first bar chart showing "Sales by Region". Where in the Power BI interface must they look to find the Sales and Region columns to create this chart?
Show answer
They should look in the Fields pane on the right side of the Power BI Desktop window. The cleaned table and its columns (Sales, Region, etc.) will be listed there, ready to be dragged onto the report canvas to create visuals.
Need to Make a Change? Returning to Power Query
What happens if you or a workshop participant loads the data and then realizes a mistake was made? For example, a column wasn't renamed, or null values weren't handled correctly.
Fortunately, Power BI makes it very easy to go back to the "kitchen." In the Home ribbon of the main Power BI Desktop window, there is a Transform data button. Clicking this will re-open the Power Query Editor, with all your queries and applied steps preserved, exactly as you left them. You can make your changes, click Close & Apply again, and Power BI will update the data model with your corrections.
Both videos you just watched demonstrate this step at the end of their segments. This "round-trip" capability is fundamental to the workflow.
Conclusion
You've now learned the crucial step of bridging the gap between data preparation and reporting. This action, while simple, is the gateway to all the powerful visualization capabilities of Power BI.
Key Takeaways for Your Workshop:
- Two Environments, One Workflow: Power Query is for preparing data; Power BI is for visualizing it.
- "Close & Apply" is the Bridge: This command finalizes your transformations and loads the clean data into the Power BI data model.
- Data Lands in the Fields Pane: Once loaded, your tables and columns appear in the Fields pane, ready to be used in reports.
- You Can Always Go Back: The "Transform data" button on the Home ribbon lets you return to the Power Query Editor to make changes at any time.
Preview of the Next Lesson:
Our clean data is now loaded and ready! In the next lesson, we will get acquainted with the Power BI report-building environment. We'll explore the main components of the Power BI Desktop interface and, most importantly, start bringing our data to life by creating our first bar charts and line charts.