Text_CSV
Power BI is a powerful business intelligence tool that allows users to analyze, visualize, and share data. One of the key features of Power BI is the ability to connect to various data sources, including text/CSV files. In this article, we will explore the Power Query M language code for connecting to text/CSV data sources from inside Power BI.
Understanding the Power Query M Language
Power Query is a data transformation and cleansing tool that is built into Power BI. The Power Query M language is the programming language used to build queries in Power Query. It is a functional programming language that is used to transform and shape data. The Power Query M language is similar to other programming languages, such as Excel formulas, SQL, and R, but it has its syntax and rules.
Connecting to Text/CSV Data Sources
Text/CSV files are a common way to store and share data. They are simple and easy to use, and they can be opened by many different applications. Power BI allows users to connect to text/CSV files and use them as a data source.
To connect to a text/CSV data source, follow these steps:
1. Open Power BI.
2. Click on “Get Data” on the Home tab.
3. Select “Text/CSV” under the “File” category.
4. Browse to the location of the text/CSV file.
5. Select the file and click “Open”.
6. The “Navigator” dialog box will appear. This dialog box shows the data in the file.
7. Select the tables you want to import, and click “Load”.
Power Query M Language Code
When you connect to a text/CSV data source, Power Query generates Power Query M language code to load the data. This code can be viewed and edited in the “Advanced Editor” in Power Query.
The following is an example of Power Query M language code for connecting to a text/CSV data source:
let
Source = Csv.Document(File.Contents(“C:TempSample.csv”),[Delimiter=”,”, Columns=4, Encoding=1252, QuoteStyle=QuoteStyle.None]),
#”Promoted Headers” = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
#”Changed Type” = Table.TransformColumnTypes(#”Promoted Headers”,{{“ID”, Int64.Type}, {“Name”, type text}, {“Date”, type date}, {“Value”, type number}})
in
#”Changed Type”
This code does the following:
1. The “let” statement initializes the data source.
2. The “Csv.Document” function is used to read the CSV file. The function takes several parameters, including the file path, delimiter, encoding, and quote style.
3. The “Promoted Headers” function is used to promote the headers of the data table.
4. The “Changed Type” function is used to change the data types of the columns in the table.
This code can be modified to suit the specific needs of the user. For example, the delimiter, encoding, and quote style can be changed, and additional transformations can be added.
Conclusion
Power Query M language code is a powerful tool for connecting to and transforming data from text/CSV data sources in Power BI. By understanding the syntax and rules of the language, users can create complex queries that can transform and shape data in any way they need. By following the steps outlined in this article, users can easily connect to text/CSV data sources and use them as a data source in Power BI.
