Udemy
    •  
    •  
    •  
    •  
    •  
    •  
    •  
    •  
Subscribe
Turn what you know into an opportunity and reach millions around the world.
Learn More
Your cart is empty.
Keep shopping
Excel PowerQuery M code language - Part 3
Rating: 4.3 out of 5(74 ratings)
635 students

Excel PowerQuery M code language - Part 3

ETL (Extract Transform Load) Data using M code - Our third part in an attempt to make you Ninja.
Created byajay parmar
Last updated 2/2023
English

What you'll learn

  • This is sequel of M code Powerquery and students are requested to first watch Part1 and Part2 also, if they are not expert in M code.
  • Solving complex Projects using M code user defined function. One of your project is solved.
  • You will learn how to work with Dates. Adding days, years,quarters will never be a challenge now.
  • How to use M Functions which helps in extracting the text values using text functions. Know your left, right, mid and Find functions in M Code with practicals.
  • How to reverse the strings How to remove the characters from a string.
  • How to insert characters into a string.
  • How to see if a string contains specific characters or not.
  • Know your List functions like List dates. LIst union. List difference and do the complex lookups between two or more than two tables without getting into hassle
  • You will see if you want to link other table headers into another tables , how to do the same. It is so amazing
  • How to extract months, days, years from a date using date functions. One should know such things as we almost have date fields in every data.
  • Know how to sum the cumulative numbers using list range function
  • Student question solved - split year and status

Course content

4 sections • 23 lectures • 6h 28m total length

  • Basic Date functions we must know Extraction and Conversions20:40
  • A quiz on date handling for you. Its fantastic20:42
  • Student question is answered with a new solution - more robust11:00

    Student raised a valid point regarding previous quiz where we calculated no of months difference between two dates. here,i am providing the solution to that year issue and this formula now can work on any dates.

  • Let us start with important Date functions13:05

    How to add days or months or years or quarters into a date.

  • My favorite List Date function25:14

    List Date function is adorable for its smoothening and easiness how it handles the dates.

  • How to combine two or more table values using List Union Function12:01
  • How to use List Intersect function - Fundamentals and Example3:54

    How to find out the common values in Multiple tables by converting them to lists. This function is another feather in your cap. Come on, let us learn it.

  • An amazing project for you on List Union Function18:03
  • Know your List Difference function and upskill your talent25:24

    List Difference is another function which can help us in looking up the data when you have more than one tables.

  • Your Question solved6:09
  • Calculate cumulative totals using List Sum and List Range functions14:00

Requirements

  • You should have Excel 2010 or above version. Best is office 365 as it provides the intellisense.
  • You must know M code basics and its Lists and records. If you do not know please refer to Part1 and Part2. Refer to document attached here for course links.
  • This part3 is only for those students who have seen part1 and part2. It is a sequel to part2.

Description

  • This Part 3 series is in continuation to Part2. To understand and make the best use of this part you must have seen part1 and part2. This is mandatory.

  • We are learning the important date functions in Mcode which helps us to work with dates in any format. We can add days,months, quaters to dates.

  • We can extract the month, day, year , day of a week using M code date functions.

  • We can find out the dates falling within a year or month or today date and many more other things.

  • DateAddDays, DateAddmonth, DateAddQtr, EoMonth, DateIsincurrentmonth etc important functions are covered in detail.

  • We are discussing about List functions like List Date, List union, List Intersect, List Difference with many practical examples.

  • How to find out the matching results in more than one tables .

  • How to union common items in more than one table.

  • How to find out the non matches in more than one table.

  • Real Projects showing the importance of List functions mentioned above.

  • How to use FIND, LEFT, RIGHT, MID Functions in M code .

  • How to use them in practical situation.

  • How to check if string has contain a specific word.

  • How to reverse the strings characters using M code Text reverse.

  • How to insert characters repeatedly into a string.

  • How to remove text from a string.

  • How to find out the length of string.

  • Convert to lower case or upper case.

  • How to remove spaces from left or right or all spaces from a string.

  • How to create a function which has multiple parameters and with that powerquery can change the data. It is a very big project.

Who this course is for:

  • Business Analysts , Excel Users and MIS analysts who like to clean, transform the large or small datasets without complex excel formulas and without any vba.
  • All those professionals and students who work with big data every day and find it challenging to shape up the most complex and unstructured data.

Report abuse