How to Work with M-Query in Power BI?

In today’s blog, we will discuss Data Analytics, Power BI, and M-Query.

The amount of data we produce every day is truly mind-boggling. There are 2.5 quintillion bytes of data created each day at our current pace, but this pace is only accelerating with the growth of the Internet of Things (IoT).

To process this much amount of data, we have some good data visualization tools such as Microsoft Power BI, Google Data Studio, Tableau, etc. M-Query is the language of Power BI which is used to get the data, transform it, model it, etc.

Following points that will be discussed in this blog:

  • Brief about the data analytics
  • Power BI visualization tool
  • M-Query
    • What is M-Query?
    • Where can we use it?
    • The basic function of M-Query
    • Examples using M-Query

Data Analytics

Data analytics is the study of breaking down crude information to make decisions about that data. Its methods can uncover patterns and measurements that would some way or another be lost in the mass of data. This data would then be utilized to enhance procedures to expand the general effectiveness of a business or framework.

It is also known as “information investigation.”

Data analytics is a very vast field. In this, we extract the information from the data and show the result in the form of charts and KPI cards.

It involves various procedures as below:

  • Extraction
  • Understanding
  • Modeling
  • Visualization

Data Extraction

In this process, we get the data either via scraping the website or via databases or Excel, CSV, etc.

Data Understanding

Once we get the data using the extraction process now its time to understand the data. In this step, we analyze the type of data and try to understand what KPI we can show using this data.

Data Modeling

In this step, we transform the data according to our requirements. Here, we perform multiple methods like pivot, unpivot, or creation of new fields based on our needs. We can do this at the source side of the destination side both.

Data Visualization

In this, we use multiple software to show insight using Charts, KPI cards, etc. Therefore, to show insights based on the client requirement we are using three software such as Power BI, Tableau, and Google Data Studio.

Visualization Tool (Power BI)

Power BI is an assortment of programming administrations, applications, and connectors that work together to transform your irrelevant wellsprings of information into intelligent, outwardly vivid, and intuitive bits of knowledge. Your information might be an Excel spreadsheet, or an assortment of cloud-put together and concerning premises half and half information distribution centers. Power BI lets you effectively interface with your information sources, picture and find what’s significant, and share that with anybody or everybody you need.

M-Query

What Is M-Query?

In summary, Microsoft Power Query gives an amazing information import experience that includes numerous highlights. Power Query works with Analysis Services, Excel, and Power BI exercise manuals. A center ability of Power Query is to channel and join, that is, to concoction information from at least one of a rich assortment of bolstered information sources. Any such information mashup is communicated utilizing the Power Query M Formula Language. It’s a practical, case touchy language like F#.

Where Can We Use It?

We can use M-Query in Power BI advance editor to get the data inside Power BI. We can transform or modify our data using Power Query (M). It is not very easy to understand but if you try to understand it then you can learn it.

Function of M-Query

There are so many functions that can help you out in your test cases.

  • Accessing data functions
  • Binary functions
  • Combiner functions
  • Comparer functions
  • Date functions
  • DateTime functions
  • DateTimeZone functions
  • Duration functions
  • Error handling
  • Expression functions
  • Function values
  • List functions
  • Lines functions
  • Logical functions
  • Number functions
  • Record functions
  • Replacer functions
  • Splitter functions
  • Table functions
  • Text functions
  • Time functions
  • Type functions
  • Uri functions
  • Value functions

Furthermore, this blog explains some of the functions in this blog and the rest of the function will explain in the next blog.

Accessing Data Functions

These functions are responsible for fetching the data. Data can be either in CSV, Excel, HTML, or Databases, etc. Below are the functions to get the data:

Fetching Data Using CSV Files
let
    Source = Csv.Document(File.Contents("C:\Users\Prashant\Desktop\projects\flights.csv\flights.csv"),[Delimiter=",", Columns=31, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"YEAR", Int64.Type}, {"MONTH", Int64.Type}, {"DAY", Int64.Type}, {"DAY_OF_WEEK", Int64.Type}, {"AIRLINE", type text}, {"FLIGHT_NUMBER", Int64.Type}, {"TAIL_NUMBER", type text}, {"ORIGIN_AIRPORT", type text}, {"DESTINATION_AIRPORT", type text}, {"SCHEDULED_DEPARTURE", Int64.Type}, {"DEPARTURE_TIME", Int64.Type}, {"DEPARTURE_DELAY", Int64.Type}, {"TAXI_OUT", Int64.Type}, {"WHEELS_OFF", Int64.Type}, {"SCHEDULED_TIME", Int64.Type}, {"ELAPSED_TIME", Int64.Type}, {"AIR_TIME", Int64.Type}, {"DISTANCE", Int64.Type}, {"WHEELS_ON", Int64.Type}, {"TAXI_IN", Int64.Type}, {"SCHEDULED_ARRIVAL", Int64.Type}, {"ARRIVAL_TIME", Int64.Type}, {"ARRIVAL_DELAY", Int64.Type}, {"DIVERTED", Int64.Type}, {"CANCELLED", Int64.Type}, {"CANCELLATION_REASON", type text}, {"AIR_SYSTEM_DELAY", Int64.Type}, {"SECURITY_DELAY", Int64.Type}, {"AIRLINE_DELAY", Int64.Type}, {"LATE_AIRCRAFT_DELAY", Int64.Type}, {"WEATHER_DELAY", Int64.Type}})
in
    #"Changed Type"

In the first step, we have fetched the data from the CSV file and then promoted the first row as a header. In the next step, we have transformed the datatype of each column and then return the changed data type columns with underlying data.

If we can see the first line here we used file. So then, Contents function to fetch the data from URL and then parse that data using CSV.Document function.

Fetching Data Using Excel
let
    Source = Excel.Workbook(File.Contents("C:\Users\Prashant\Desktop\studentv1.xlsx"), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Date", type text}, {"Column2", type text}, {"Name", type text}, {"Roll No.", Int64.Type}, {"Guardian's name", type text}, {"Class", type text}, {"Section", type text}, {"Attendance(%)", Int64.Type}, {"Half yearly result", Int64.Type}, {"Final term result", Int64.Type}, {"Yearly result", type number}, {"Class Rank", Int64.Type}, {"Class And Section Rank", Int64.Type}, {"Section Rank", Int64.Type}, {"Chosen Sport", type text}, {"House", type text}, {"Class Teacher", type text}, {"Status", type text}})
in
    #"Changed Type"

In the above example, an excel file is considered to fetch the data in which Excel.Workbook is used instead of CSV.Document. Also, in the excel file there is a possibility of multiple data tabs so, first, the Data tab is selected. In the second step, sheet1 as a data tab is selected. The next step is the same as in the case of CSV files.

Date Manipulation Functions

Considering the above example and some Date manipulation function in that code is performed. Below is the combined code:

let
    Source = Excel.Workbook(File.Contents("C:\Users\Prashant\Desktop\studentv1.xlsx"), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Date", type text}, {"Column2", type text}, {"Name", type text}, {"Roll No.", Int64.Type}, {"Guardian's name", type text}, {"Class", type text}, {"Section", type text}, {"Attendance(%)", Int64.Type}, {"Half yearly result", Int64.Type}, {"Final term result", Int64.Type}, {"Yearly result", type number}, {"Class Rank", Int64.Type}, {"Class And Section Rank", Int64.Type}, {"Section Rank", Int64.Type}, {"Chosen Sport", type text}, {"House", type text}, {"Class Teacher", type text}, {"Status", type text}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Changed Type",{"Status", "Class Teacher", "House", "Chosen Sport", "Section Rank", "Class And Section Rank", "Class Rank", "Yearly result", "Final term result", "Half yearly result", "Attendance(%)", "Section", "Class", "Guardian's name", "Roll No.", "Name", "Column2", "Date"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Other Columns",{{"Column2", "Email"}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Renamed Columns", "Date", Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv), {"Date.1", "Date.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Date.1", Int64.Type}, {"Date.2", Int64.Type}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"Date.1"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Removed Columns",{{"Date.2", type date}}),
    #"Removed Blank Rows" = Table.SelectRows(#"Changed Type2", each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null}))),
    #"Duplicated Column" = Table.DuplicateColumn(#"Removed Blank Rows", "Date.2", "Date.2 - Copy"),
    #"Renamed Columns1" = Table.RenameColumns(#"Duplicated Column",{{"Date.2", "Date"}, {"Date.2 - Copy", "EOM"}}),
    #"Calculated End of Month" = Table.TransformColumns(#"Renamed Columns1",{{"EOM", Date.EndOfMonth, type date}}),
    #"Duplicated Column1" = Table.DuplicateColumn(#"Calculated End of Month", "Date", "Date - Copy"),
    #"Renamed Columns2" = Table.RenameColumns(#"Duplicated Column1",{{"Date - Copy", "SOM"}}),
    #"Calculated Start of Month" = Table.TransformColumns(#"Renamed Columns2",{{"SOM", Date.StartOfMonth, type date}}),
    #"Duplicated Column2" = Table.DuplicateColumn(#"Calculated Start of Month", "Date", "Date - Copy"),
    #"Renamed Columns3" = Table.RenameColumns(#"Duplicated Column2",{{"Date - Copy", "Quarter"}}),
    #"Calculated Quarter" = Table.TransformColumns(#"Renamed Columns3",{{"Quarter", Date.QuarterOfYear, Int64.Type}}),
    #"Duplicated Column3" = Table.DuplicateColumn(#"Calculated Quarter", "Date", "Date - Copy"),
    #"Renamed Columns4" = Table.RenameColumns(#"Duplicated Column3",{{"Date - Copy", "WOY"}, {"Quarter", "QOY"}}),
    #"Calculated Week of Year" = Table.TransformColumns(#"Renamed Columns4",{{"WOY", Date.WeekOfYear, Int64.Type}}),
    #"Duplicated Column4" = Table.DuplicateColumn(#"Calculated Week of Year", "Date", "Date - Copy"),
    #"Renamed Columns5" = Table.RenameColumns(#"Duplicated Column4",{{"Date - Copy", "WOM"}}),
    #"Calculated Week of Month" = Table.TransformColumns(#"Renamed Columns5",{{"WOM", Date.WeekOfMonth, Int64.Type}})
in
    #"Calculated Week of Month"

In the above example, many functions have been used that will help you to understand the M-Query. Below is the explanation of each of them.

  • Table.PromoteHeaders: Though, by using this function we have set the header name of each column. Here you need to set some of the parameters that have been mentioned in the above example.
  • Table.TransformColumnTypes: This formula helps you to change the data type of existing columns. Please refer to the above example.
  • Table.SelectColumns: Using this formula you can select whatever the columns you needed.
  • Table.RenameColumns: This formula helps you to rename the column name. Please refer to the above example.
  • Table.SplitColumn: If you want to split the value based on the Delimiter use this formula. Please refer to the above example.
  • Table.RemoveColumns: Therefore, to remove unwanted columns use this formula as we have removed some extra columns that came while fetching the data. Furthermore, please, refer to the above example.
  • Table.SelectRows: Here we have used this function in a combination of each and list function to remove the blank or null value rows. Please refer to the above example.
  • Table.DuplicateColumn: We can use this function to duplicate the columns. Please refer to the above example.
  • Table.TransformColumns: We have used this function to create the start of the month, end of the month, quarter of the year, week of the month, and week of the year. Please refer to the above example.

The rest of the functions will be covered in some other blog later.

Conclusion

This blog is just to spread the knowledge about data analytics, visualization tool, and M-Query. In upcoming years this data analytics field will boom in terms of a career. This blog, explained Data Analytics, Power BI Tool, and the most useful language in Power BI which is M-Query. If you have any questions or suggestions then please comment down in the comment box.

To know more about our services please visit: https://www.loginworks.com/index

 

Leave a Comment