HdInsight.Containers

HdInsight.Containers

D

The M Code Behind the Power Query M function HdInsight.Containers

What is Power Query M?

Power Query M is a powerful tool that allows you to transform and clean data before importing it into Excel or Power BI. Power Query M is based on a functional programming language called “M”. M is used to define queries in Power Query and is a powerful language that allows you to manipulate and transform data in many ways.

What is HdInsight.Containers Function?

HdInsight.Containers is a Power Query M function that allows you to access data stored in Azure Blob Storage, Azure Data Lake Storage, and Hadoop Distributed File System (HDFS) through the HDInsight cluster. The function provides a simple way to import external data into Power Query and perform ETL processes on it. The function requires authentication to access the data source, which can be done through Azure Active Directory or key-based authentication.

The M Code Behind the HdInsight.Containers Function

When you use the HdInsight.Containers function, Power Query generates M code behind the scenes. The M code generated by Power Query defines the steps required to access the external data source and transform it as required. Here is an example of the M code generated by Power Query when using the HdInsight.Containers function:


let

Source = HdInsight.Containers(“storageaccount.blob.core.windows.net”, “containername”, [HierarchicalNavigation=true]),

#”blobname.csv” = Source{[Name=”blobname.csv”]}[Content],

#”Imported CSV” = Csv.Document(#”blobname.csv”,[Delimiter=”,”, Columns=7, Encoding=1252, QuoteStyle=QuoteStyle.None]),

#”Promoted Headers” = Table.PromoteHeaders(#”Imported CSV”, [PromoteAllScalars=true]),

#”Changed Type” = Table.TransformColumnTypes(#”Promoted Headers”,{{“Column1”, type text}, {“Column2”, type text}, {“Column3”, type text}, {“Column4”, type text}, {“Column5”, type text}, {“Column6”, type text}, {“Column7”, type text}})

in

#”Changed Type”


The M code above shows the steps required to access a CSV file stored in an Azure Blob Storage container. The code retrieves the content of the CSV file, imports the content into Power Query, promotes the headers, and finally changes the data types of the columns as required.

How to Use the HdInsight.Containers Function

To use the HdInsight.Containers function, you must first authenticate to the external data source. Authentication can be done through Azure Active Directory or key-based authentication. Once you have authenticated, you can use the function to access the data stored in the external data source.

Here is an example of using the HdInsight.Containers function to access data stored in an Azure Blob Storage container:


let

Source = HdInsight.Containers(“storageaccount.blob.core.windows.net”, “containername”, [HierarchicalNavigation=true]),

#”blobname.csv” = Source{[Name=”blobname.csv”]}[Content],

#”Imported CSV” = Csv.Document(#”blobname.csv”,[Delimiter=”,”, Columns=7, Encoding=1252, QuoteStyle=QuoteStyle.None]),

#”Promoted Headers” = Table.PromoteHeaders(#”Imported CSV”, [PromoteAllScalars=true]),

#”Changed Type” = Table.TransformColumnTypes(#”Promoted Headers”,{{“Column1”, type text}, {“Column2”, type text}, {“Column3”, type text}, {“Column4”, type text}, {“Column5”, type text}, {“Column6”, type text}, {“Column7”, type text}})

in

#”Changed Type”


In the example above, we are accessing a CSV file stored in an Azure Blob Storage container. The function retrieves the content of the CSV file, imports the content into Power Query, promotes the headers, and finally changes the data types of the columns as required.

The HdInsight.Containers function is a powerful tool that allows you to access external data stored in Azure Blob Storage, Azure Data Lake Storage, and Hadoop Distributed File System (HDFS) through the HDInsight cluster. The function generates M code behind the scenes that defines the steps required to access and transform the external data. By using this function, you can handle big data efficiently and perform ETL processes on it.

Power Query and M Training Courses by G Com Solutions (0800 998 9248)

Upcoming Courses

Contact Us

Subject

Your Name (required)

Company/Organisation

Email (required)

Telephone

Training Course(s)

Your Message

Upload Example Document(s) (Zip multiple files)

Copyright, G Com Solutions Ltd, 2024.
Tower Bridge Business Centre, 46-48 East Smithfield, London E1W 1AW
0800 998 9248   |   9:00 a.m. till 5:30 p.m.

Connect With Me: