Dive into M Code For More Power Query

March 01, 2023 - by Bill Jelen

Problem: My data is weird. I have to do something that the Power Query interface can not do.

Strategy: The Power Query interface provides an easy way for you to write steps in the M programming language. There are many things possible in M that can not be done in the interface. For example, I had a person in a seminar where their CSV files started with 4 rows of identifying information. The actual headings for the data started in row 5. This was causing the Power Query interface to fail, assuming there were only two columns in the data instead of 10 columns.

I reached out to Miguel Escobar of PowerQuery.Training. He sent me some M code that I could type in the Advanced Editor found on the View tab in Power Query.

Some M code as viewed in the Advanced Editor. There is a line that says
skip = List.Skip(src, 5),
This is the line that tells Power Query to skip the first five rows. — Figure 1043. Someone on a forum somewhere will know the M code to solve your problem.

Honestly, I don't know how to write in the M programming language and I never intend to learn. I can see the "5" in the List.Skip line is the magic. Knowing that it is possible to solve problems in M will let me Google for a solution.

This article is an excerpt from Power Excel With MrExcel

Title photo by James Healy on Unsplash