M Code is a date

Good Morning @Melissa,

You are absolutely an Angel :). I really appreciate your help. It’s an absolutely pleasure to learn from the best!

It’s a shame there is not much on Enterprise DNA regarding M-Code.

I haven’t tested the file on smaller data set as when I looked again into your formula there is applied logic and this is how I understand:

The main to applied steps are:

  1. Group the data per Control Number, as in the raw data each control number is separated but can have one or multiple entries. By grouping the data you have created additional table which is used in the M code.

  2. Your Master M-Code formula = if Table.RowCount([AllRows]) >1 then Table.Skip(Table.FillDown([AllRows],{“Period”, “Date”}), 1) else [AllRows]

The main part is If there count is greater than one. The False Logic will keep the data if there a single row unit/transaction whereas the True logic removes the first row (the total) and keeps the split of the total, but before it removes the total row columns it filter period and date down :slight_smile:

Please do correct me if I misunderstood the formula. As I said I am not even beginner, I am Newby to M code as I never to be honest used a part from small few formulas. I am really fascinated with M-Code!

Thank you for respond regarding parameters.

I never used paramers only when to filter the data by relevant group i.e Product or region.

I went to query editor and created parameter like you but then in order to make it work did you open a blank query and type Source = Filelocation2?

image

Hi @Matty,

Excellent and you’ve got the logic down :+1: Great job on formulating it in your own words that can really be helpful in understanding. You’ll find other tips in this topic.

.

As for the FileLocation Parameter maybe this video wil be helpful, I showcase it around the 2 min mark.

I hope this is helpful.

2 Likes

Oh great,

I just watched the first 5 mins, create parameter then upload an excel file and then change a source! I will watch full video after work :slight_smile:

I definitely will look at the link regarding m Code and read it though :slight_smile:

Once again thank you for all your help!