Power BI – Extract Tables Using Examples
By: David Rohlfs
Introduction
Whether you are working with cumbersome CSV files or simply want to streamline your ETL process, the Extract Tables Using Examples function in Power BI can be incredibly useful. This function allows you to use an example table to automatically determine the necessary transformations for a file. This blog will focus on when this function should be utilized and provide a walkthrough demonstrating its use.
When to Use It
Extract Tables Using Examples in Microsoft Power BI is a powerful feature that simplifies the transformation of raw or semi-structured data, such as CSV files, into clean and usable tables. When you connect to a CSV file, especially one that contains unstructured information, irregular columns, or embedded headers, it can be challenging to extract only the rows and columns you need for analysis. With this function, you can guide Power BI by providing sample outputs—essentially, you show the system a few rows that represent the desired outcome. Power BI’s AI-driven algorithms then infer patterns from your examples and automatically generate transformation steps to extract the relevant data across the entire file. This reduces the need for complex manual editing or advanced coding in Power Query.
This approach works particularly well with CSVs that have inconsistent formats, such as multi-line headers or extra commentary rows. Instead of spending valuable time reshaping your data with traditional methods, Extract Tables Using Examples accelerates the process, making it more intuitive and accessible, even for those with minimal technical experience. The result is a streamlined workflow, allowing you to focus more on analysis and insights rather than data preparation.
The world of Power BI is constantly evolving, and this function is no exception. While I currently see examples of this function in the CSV file connector, I have observed similar capabilities with the Web connector and expect this trend to continue. The main reason all connectors do not have this ability is that the data is not always immediately ingestible for visualization in Power BI. Often, you choose a connector that requires you to look through folders or parameterize before fully exploring the transformation process. Think of this with the Extract, Transform, and Load (ETL) process in mind; you cannot perform the Transform process before the Extraction of the data.
Walkthrough
In Microsoft Power BI, go to Get Data and then choose the Text/CSV connector.
Find your file and click Open.
This is the normal pop-out for the CSV connector. Here, choose the button in the bottom left that says, “Extract tables using example.”
This is the new view to enter your example. Notice that the top box shows the extracted data. This is what you will need to build from so that the AI model in the background knows what you mean (Basically, use the data from the top box into the bottom example box).
If you already have an example table, you can copy and paste it here. For this example, I will enter the column names and the first row of data. This will provide enough information for our example to be correct. Understand that the more complex the table and transformations to get to your example, the more example data you will likely need to enter, and the more likely it is that this will not work.
Once you have finished with your example table, click Load in the bottom right corner. This will automatically put this table through the ETL process and prepare it for visualizations.
Now inside of Power Query (Transform Data), this is what our query looks like. Notice that the Applied Steps already include transformations; this is what the function determined was needed to process our CSV file.
Conclusion
While you don’t always need things to be simplified, being able to use an example table to ingest your data is a great feature! Often, you may choose not to use this function because it is a new way of doing things, but if you encounter issues with CSV files ingesting into Power BI, the Extract Tables Using Examples function could become valuable. If you enjoyed this blog, please check out some of the other links below.
Links Related to This Blog:
Comments
Leave a Reply
You must be logged in to post a comment.