Skip Navigation Links.
🏠Home
Collapse 💻Apps💻Apps
🍵HTML Editor
🍵Unicode Symbols
Collapse 📂Topics📂Topics
Collapse 📄Data Analysis Expressions ...📄Data Analysis Expressions ...
Collapse 📄Complete List of DAX Fun...📄Complete List of DAX Fun...
📄ABS...
📄ACCRINT...
📄ACCRINTM...
📄ACOS...
📄ACOSH...
📄ACOT...
📄ACOTH...
📄ADDCOLUMNS...
📄Using DAX to split colum...
Collapse 📄MS Office...📄MS Office...
📄MS Access...
Collapse 📄MS Excel...📄MS Excel...
Collapse 📄Ribbon...📄Ribbon...
Collapse 📄Home...📄Home...
📄Alignment...
📄Cells...
📄Clipboard...
📄Editing...
📄Font...
📄Number...
📄Styles...
Collapse 📄Insert...📄Insert...
📄Charts...
📄Illustrations...
📄Tables...
Collapse 📄Office Scripts...📄Office Scripts...
📄VBA to TypeScript: A S...
Collapse 📄Power BI...📄Power BI...
Collapse 📄Get Data...📄Get Data...
📄Get Data from Files...
📄View any visual as table...
Collapse 📄Power Query / M Language...📄Power Query / M Language...
📄Entering Data without Da...
📄Data Source Settings...
Collapse 📄Practice Datasets...📄Practice Datasets...
📄Kaggle Datasets...
Collapse 📄SQL Datasets...📄SQL Datasets...
📄Adventure Works (SQL S...
📄Star Bakery (MySQL Ser...
📄Star Corp (SQL Server)...
Collapse 📄Python...📄Python...
📄Fill missing values in M...
📄List of Machine Learning...
📄Python for MS Excel...
📄Seasonal Autoregressive ...
📄Working with Random Valu...
Collapse 📹Video Tutorials📹Video Tutorials
🎬Power BI: Creating Busines...
🎬Analysing Data in SQL Serv...
🎬Time Intelligence in Power...
🎬SQL Magic: Auto-Generating...
🎬Using Gemini for SQL Data ...
🎬Using Dropdown Lists Effec...
🎬Build a Stunning Animated ...
🎬Power BI: Creating and Int...
🎬Advanced Power BI Realtime...
🎬Real time Monitoring with ...
🎬Creating and Using Flow Ma...
🎬How to create terrain, add...
🎬Game Design Tutorial for B...
🎬Dashboard with Relational ...
🎬Power Pivot and Data Model...
🎬Selecting Project using Pr...
🎬Two Ways to Use Custom SQL...
🎬Diversity, Equity, and Inc...
🎬Complete Guide to Using Dr...
🎬Gantt Chart in Power BI: C...
🎬Monitoring Progress Using ...
🎬Creating a Dashboard of Sa...
🎬Indigeno Technologies - Pa...
🎬Creating and Publishing We...
🎬Make Your Data Grow: Tree ...
🎬Power BI: Adding Historica...
🎬We asked an AI to create a...
🎬Power BI: Staff Engagement...
🎬Using Unicode Symbols in E...
🎬Power BI: Timesheet Analys...
🎬Time Machine for Your Data...
🎬Azure Data Studio - Comple...
🎬Power BI: Creating a Stunn...
🎬Power BI: Project Payback,...
🎬Power BI: Complete Realtim...
🎬Power BI: DAX Query View f...
🎬Build Your Own Excel Toolb...
🎬Data Science Programming: ...
🎬Power BI: Manage and Track...
🎬MS Excel: Using Images in ...
🎬MS Excel: Complete Guide t...
🎬Power BI: Creating Dashboa...
🎬Power BI: Using Latest Pre...
🎬Power BI: Performing Compa...
🎬Power BI: Creating Dashboa...
🎬Python: Forecasting with S...
🎬Power BI: Creating a Dice ...
🎬MS Excel: Creating a Dynam...
🎬Power BI: Create stunningl...
🎬MS Excel: Data Visualizati...
🎬Power BI: Assets Managemen...
🎬Power BI: Using Copilot in...
🎬Power BI: How to Create a ...
🎬MS Excel: Complete Guide t...
🎬Power BI: Easily Pivoting ...
🎬Power BI: How to Create a ...
🎬MS Excel: Using Python Rea...
🎬MS Excel: Realtime/Live Da...
🎬Power BI: Creating Paginat...
🎬Power BI: Exploring PBI On...
🎬MS Excel: Mastering MS Exc...
🎬MS Excel: Performing Non-L...
🎬MS Excel: How to add lates...
🎬Power BI: Performing PESTL...
🎬Power BI: Creating an Exot...
🎬Python: Resizing and Conve...
🎬Power BI:The Clock - Episo...
🎬MS Power Point: The Clock ...
🎬Looker Studio: Setting up ...
🎬HTML: Complete Course for ...
🎬Power BI: Creating a Power...
🎬MS Excel: Best Way to Crea...
🎬Power BI: Setting up Repor...
🎬Power BI: Real-time Data R...
🎬MS Excel: Mastering Excel ...
🎬SQL: Creating Realtime Liv...
🎬SQL: SQL Server Reporting ...
🎬Power BI: Data Modeling an...
🎬MS Excel: Data Modeling an...
🎬Python: Mastering NumPy Li...
🎬Power BI: Automating Data ...
🎬Power BI: How to Build a P...
🎬Power BI: Displaying Realt...
🎬Power BI: Unlocking Insigh...
🎬Power BI: How to Create a ...
🎬Power BI: Comparing Variou...
🎬Power BI: Animate Your Vis...
🎬MS Excel: Using Formulas l...
🎬Power BI: Using Folders as...
🎬Power BI: Using Folders as...
🎬Python: Uploading Files Di...
🎬Power BI: Creating Stunnin...
🎬Power BI: Integrating Pict...
🎬Power BI: Crafting Dynamic...
🎬Power BI: Dealing with Err...
🎬Power BI: Creating Dynamic...
🎬MS Excel: Calculating, Com...
🎬Power BI: Exploratory Data...
🎬Power BI: Create an Intera...
🎬Python: Creating Polynomia...
🎬Looker Studio: Creating Dy...
🎬Google Charts: Using Googl...
🎬Looker Studio: Understandi...
🎬Python: Mastering Data Vis...
🎬Power BI: Realtime Simulat...
🎬Power BI: Connecting to an...
🎬Python: Performing basic S...
🎬MS Excel: Simplifying Comp...
🎬Power BI: How to Create Hi...
🎬MS Excel: How to Use Calcu...
🎬Power BI: Using Small Mult...
🎬Power BI: Performing Finan...
🎬Power BI: Displaying SQL D...
🎬Power BI: Displaying Audit...
🎬Power BI: Loading Data int...
🎬Power BI: Using SQL Query ...
🎬Power BI: Visualizing Basi...
🎬MS Excel: Create a Smart, ...
🎬Power BI: Realtime Plant S...
🎬Power BI: Analyzing Season...
🎬Power BI: Creating your fi...
🎬Power BI: Creating Paginat...
🎬Power BI: Creating a Key P...
🎬Power BI: Creating Data So...
🎬Power BI: Setting up Power...
🎬Creating and Setting up a ...
🎬Power BI: Creating Pareto ...
🎬Power BI: Realtime Call Ce...
🎬Power BI: Creating a Proje...
🎬Power BI: Displaying Live ...
🎬Power BI: Using Conditiona...
🎬MS Excel: How to Create Re...
🎬Power BI: Realtime Sales S...
🎬Power BI: Displaying Realt...
🎬MS Excel: Using Excel Slic...
🎬Tableau: Tutorial for Begi...
🎬Power BI: How to Use Bookm...
🎬MS Access: How to Create R...
🎬MS Excel: Using What If An...
🎬Power BI: Create a YouTube...
🎬Power BI: Correlation Anal...
🎬Power BI: Analyzing Murder...
🎬Power BI: Manage and Monit...
🎬MS Excel: Creating an Anim...
🎬MS Excel: Creating 3D Maps...
🎬Power BI: Analyzing Wareho...
🎬MS Excel: How to add seria...
🎬Power BI: Mastering Market...
🎬MS Excel: What lies beneat...
🎬MS Excel/Power BI: Unpivot...
🎬Power BI: Exploring Defaul...
🎬MS Excel: Using the Break-...
🎬MS Excel: Predicting Sales...
🎬Power BI: Market Basket An...
🎬MS Excel: Creating a Bell ...
🎬Power BI: Creating a Dimen...
🎬Looker Studio: Creating a ...
🎬R: Creating Your First Pie...
🎬MS Excel: Conditional Form...
🎬Power BI: Analyzing human ...
🎬Python: Unleashing the Pow...
🎬Excel: Mapping, Grouping a...
🎬Power BI: Forecasting Data...
🎬Power BI: Grouping Data in...
🎬JavaScript: Using the if-e...
🎬Power BI: Connecting to th...
🎬JavaScript: Performing Mat...
🎬Power BI: Analyzing Bank L...
🎬MS Excel: Pivot... Pivot.....
🎬JavaScript: How to use onC...
🎬Power BI: Using Sankey Dia...
🎬Power BI: Multi Page Power...
🎬JavaScript: How to use onL...
🎬JavaScript: How to get the...
🎬Power BI: Crimes in Los An...
🎬Power BI: Crimes in Los An...
🎬Excel: Using the Fill Feat...
🎬Power BI: Drill-through Re...
🎬Power BI: Using DAX (Data ...
🎬Power BI: Using Smart Narr...
🎬Power BI: Bank Failure in ...
🎬Python: Predicting absente...
🎬Power BI: Dashboards with ...
🎬Power BI: Team and Product...
🎬Excel: Lookup Functions, V...
🎬Power BI: Artificial Intel...
🎬Power BI: Drill Down Repor...
🎬Python: Creating a GUI bas...
🎬Power BI: Visualizing Stoc...
🎬Python: Expected Returns (...
🎬Power BI: Decomposition Tr...
🎬Power BI: Sales and Profit...
🎬Python: Why you should not...
🎬Python: How to save, reloa...
🎬Power BI: Dimensional Mode...
🎬Python: How to classify da...
🎬Python: Machine Learning, ...
🎬Python: Web Scraping using...
🎬Animate: Classic Tween in ...
🎬Python: Support Vector Mac...
🎬Python: How to perform loo...
🎬Unity3d: How to create ter...
🎬Python: How to create and ...
🎬Python: How to work with a...
🎬Python: How to work with p...
🎬Python: How to work with S...
🎬SQL: How to create databas...
🎬Python: Data Scraping from...
🎬MS Excel: Pivot Tables, Pi...
🎬Python: How to use World B...
🎬MS Excel: How to use COUPD...
🎬Power BI: How to install P...
🎬Python: Easiest way to dow...
🎬MS Excel: How to use AMORD...
🎬MS Excel: How to use ACCRI...
🎬Python: Calculating varian...
🎬MS Excel: How to calculate...
🎬Python: How to calculate a...
🎬MS Excel: How to calculate...
🎬Python: How to calculate a...
🎬MS Excel: How to calculate...
🎬Python: How to calculate a...
🎬MS Excel: How to calculate...

📄Get Data from Files

Power BI is a powerful business intelligence tool developed by Microsoft that allows you to connect to various data sources, including files such as Excel workbooks, CSV files, text files, JSON files, XML files, and more. You can use Power BI’s data transformation capabilities to clean, transform, and shape the data from these files before creating visualizations and reports.

Image

You can get data from following files.

Excel Workbook
An Excel workbook is a file created using Microsoft Excel, which is a widely used spreadsheet application developed by Microsoft. Excel workbooks are used to store and organize data in a structured manner, allowing users to perform calculations, create charts and graphs, analyze data, and create reports. Excel workbooks are commonly used for a wide range of tasks, including financial analysis, budgeting, project management, data tracking, and reporting. They are popular in business, education, research, and personal use due to their versatility and user-friendly interface. An Excel workbook can contain multiple worksheets, also known as spreadsheets or tabs. Each worksheet consists of rows and columns, forming a grid of cells where data can be entered. In an Excel worksheet, data is organized in the form of rows and columns.

Text/CSV
Text/CSV (Comma-Separated Values) is a file format used to store and exchange tabular data in a plain text format. It is a widely used format for representing structured data, such as spreadsheets or databases, where each row of data is represented as a line of text, and individual values within each row are separated by commas (or other delimiters). It is also one of the oldest and simplest way of storing and exchanging data, and the text/csv files are almost universally supported across applications.

In a Text/CSV file each line typically corresponds to a single record or row of data. Within each line, data values for different columns are separated by commas (or semicolons, tabs, or other delimiter characters, depending on the chosen delimiter). The first row of the file is often used to specify column headers, providing a label for each column of data.

Here is an example of a text/csv file:
name,age
john,22
jack,30
kate,28

In this example, the first row (name and age) provides the column headers, and the subsequent rows contain the corresponding data for each person.

CSV files are versatile and easy to work with because they are plain text files, which can be opened and edited using a simple text editor or a specialized software application like Microsoft Excel, Google Sheets, or database management tools. Many software applications support importing and exporting data in CSV format, making it a common choice for data interchange between different systems and applications.

When working with data analysis tools like Microsoft Power BI, you can connect to Text/CSV files to import data, perform transformations, and create visualizations. Similarly, programming languages like Python provide libraries (e.g., csv module) to read and manipulate CSV files, which is useful for data processing tasks.

It’s worth noting that while CSV is a simple and widely supported format, it may have limitations in handling more complex data structures compared to other formats like Excel workbooks or databases. Additionally, special considerations may be needed when working with CSV files that contain special characters, line breaks, or data types requiring specific formatting.

XML
XML (eXtensible Markup Language) is a markup language commonly used for structuring and representing data in a hierarchical format. It is designed to be both human-readable and machine-readable, making it a versatile choice for data processing, storage, and transfer, especially when dealing with complex or semi-structured data.

XML data is organized in a hierarchical structure that consists of elements and attributes. Elements can have child elements and text content. This structure makes it suitable for representing nested or related data. XML can represent a wide variety of data types, including text, numbers, dates, and even more complex data structures like lists and nested records. This makes it suitable for scenarios where data doesn’t fit neatly into a tabular format. XML is extensible, meaning you can define your own tags and structure to represent your data. This is particularly useful when dealing with data that doesn’t conform to a predefined schema. XML data can be processed using various programming languages and libraries. For example, you can use libraries like xml2 in R, xml.etree.ElementTree in Python, or built-in XML processing functions in languages like Java and C#. These tools allow you to parse, extract, and manipulate XML data.

Power BI supports importing and processing XML data, but it may require some data transformation to fit the XML structure into a tabular format suitable for analysis and visualization. You can use Power Query, which is integrated into Power BI, to transform XML data during the data loading process. Power Query provides tools to extract specific elements, attributes, and text content from XML files and transform them into usable columns and rows. XML is often used in web services and APIs for data exchange. Many APIs use XML to structure the data they provide or consume. Power BI can connect to web services that return XML data and transform it into a format suitable for reporting and analysis. While XML is flexible, it can be verbose and may not be as efficient for large datasets as more compact formats like JSON. JSON (JavaScript Object Notation) is another commonly used format for data transfer and is often preferred for its simplicity and efficiency. When working with XML data in Power BI, it’s important to have an understanding of XML’s structure and how to transform it into a suitable format for analysis. You may need to use Power Query’s capabilities to extract and shape the data before building visualizations and reports in Power BI.

JSON
JSON (JavaScript Object Notation) is a lightweight data interchange format that is widely used for storing, structuring, and transmitting data between a server and a client, or between different software systems. JSON is both human-readable and machine-readable, making it a popular choice for data representation and exchange, especially in web applications and APIs.

JSON data is organized in a key-value pair format. It consists of objects, where each object is enclosed in curly braces {}, and each key is associated with a value. Values can be strings, numbers, booleans, arrays, or nested objects. JSON arrays are ordered lists of values enclosed in square brackets []. Arrays can contain multiple objects, each represented by its own set of key-value pairs. JSON is easy for both humans and machines to understand. Its simple structure makes it suitable for representing complex data structures in a compact and readable manner.

Power BI supports importing and processing JSON data. You can connect Power BI to JSON files or APIs that return JSON data. Power Query, the data transformation tool integrated into Power BI, allows you to extract, transform, and load (ETL) JSON data. You can flatten nested structures, filter and reshape data, and convert JSON data into tabular form suitable for analysis. Many web services and APIs use JSON to deliver data to clients, including Power BI. Power BI can connect to these APIs, retrieve JSON data, and transform it into a format that can be visualized and analyzed within the Power BI interface. Power Query provides a rich set of functions for working with JSON data. You can use functions like Json.Document to parse JSON strings, Table.FromRecords to convert JSON arrays into tables, and other functions to navigate and extract specific data elements. After importing JSON data into Power BI and transforming it, you can build data models, create relationships, and generate visualizations and reports based on the transformed JSON data. JSON data can be more compact than XML, resulting in faster data transfers. However, when dealing with large datasets, it’s important to consider data transformation and performance optimization techniques to ensure efficient processing in Power BI.

JSON has become a standard data format for many modern applications and services, and its integration with Power BI enables users to work with a wide range of data sources and APIs seamlessly.

Folder
In Power BI, the concept of a “folder” is used within the “Get Data” functionality to refer to a directory on your local computer or a network location where one or more files are stored. The ability to connect to a folder and combine data from multiple files within that folder is a powerful feature that simplifies data preparation and analysis.

Folders are often used when dealing with scenarios where data is split across multiple files. For example:
> You have monthly sales reports saved as individual Excel files in a folder.
> Log files containing data for each day stored in separate text files within a folder.
> CSV files with data from different regions or departments stored in a common directory.

When working with folders, it’s important to maintain a consistent naming convention and file structure to ensure that Power BI can effectively combine and transform the data. For example, if you’re dealing with monthly sales reports, having a clear and consistent naming pattern (e.g., “Sales_January.xlsx,” “Sales_February.xlsx,” and so on) will help Power BI recognize and combine the data accurately. When connecting to a folder in Power BI, you provide the path to the directory. Power BI will then scan the folder and attempt to load data from all files within that folder based on the specified options. You can configure options such as file extensions, filters, and data transformation steps. After connecting to a folder, you can use Power Query, Power BI’s data transformation tool, to further shape and clean the data. You might need to combine, filter, pivot, or apply other transformations to prepare the data for visualization. Power BI allows you to schedule automatic data refreshes, so your reports and dashboards stay up to date with the latest data in the folder. When new files are added to the folder or existing files are updated, Power BI can refresh the data based on your settings.

Connecting to a folder in Power BI is a powerful way to streamline data preparation when dealing with multiple files. It simplifies the process of combining and transforming data from various sources within a folder structure, allowing you to create insightful reports and visualizations.

PDF
PDF support in Power BI was first introduced in the Power BI Desktop November 2019 release. The PDF connector is a preview feature, which means that it is still under development and may not be fully supported in all cases. The PDF connector allows you to import data from PDF files into Power BI.

To use the PDF connector, you first need to open the Power BI Desktop and connect to a PDF file. You can do this by clicking on the Get Data button and selecting the PDF File connector. Once you have connected to a PDF file, the PDF connector will scan the file for tables and other data structures. It will then display a list of the tables that it found in the Navigator pane. You can select the tables that you want to import into Power BI and then click on the Load button. The PDF connector will import the data from the selected tables into Power BI. The data will be stored in a Power BI dataset, which you can then use to create reports and dashboards.

It is important to note that the PDF connector is not perfect. It may not be able to import all PDF files, and it may not be able to import all of the data from a PDF file. If you encounter any problems with the PDF connector, you can try using a different method to import the data into Power BI, such as using the Web connector to connect to the PDF file online. In addition to the PDF connector, Power BI also supports exporting reports and dashboards to PDF files. This can be useful for sharing reports with people who do not have Power BI. To export a report to PDF, you can click on the Export button in the Power BI Desktop and select the PDF format.

Parquet
Parquet is a columnar data storage format that is optimized for analytical workloads. It is a popular format for storing data in data lakes and warehouses.

In Power BI, the Parquet connector allows you to connect to Parquet files and import the data into Power BI. To use the Parquet connector, you first need to open the Power BI Desktop and connect to a Parquet file. You can do this by clicking on the Get Data button and selecting the Parquet connector. Once you have connected to a Parquet file, the Parquet connector will scan the file for tables and other data structures. It will then display a list of the tables that it found in the Navigator pane. You can select the tables that you want to import into Power BI and then click on the Load button. The Parquet connector will import the data from the selected tables into Power BI. The data will be stored in a Power BI dataset, which you can then use to create reports and dashboards.

The Parquet connector is a powerful tool for importing data into Power BI. It is especially useful for large datasets and historical datasets. If you have data that is stored in Parquet format, the Parquet connector is a great way to import it into Power BI. Parquet files are compressed, which can save you time and bandwidth when importing data. These files are columnar, which can improve the performance of queries on large datasets. They are self-describing, which means that Power BI can automatically detect the schema of the data when you import it.

SharePoint folder
A SharePoint folder is a logical container for files and folders that are stored in SharePoint Online or SharePoint Server. In Power BI Desktop, you can connect to a SharePoint folder and import the data from all of the files in the folder into a Power BI dataset. This can be a great way to import data from a variety of sources, such as Excel spreadsheets, PDF files, and text files.

SharePoint folders are a great way to organize data. You can create folders for different types of data, such as sales data, marketing data, and customer data. These folders are easy to share. You can share a SharePoint folder with other users by giving them access to the folder. This can be a great way to collaborate on data projects. They are secure. You can control who has access to a SharePoint folder by assigning permissions to the folder. This can help to protect your data from unauthorized access.