M Code is a date

Hi DNA Team,

I will appreciate your help as I am not familiar with the M-Code.

I would like to create an M-code base on the period column:

If Period is a date then 1 else 0, but I don’t know which m code function I could use.

Transformation.pbix (53.0 KB)
Your help will be grateful


You don’t need to know any M Code to do this. Just go to the PQ menu, and select Add Column and then Conditional Column. A form will pop up, and you just fill in the blanks, and it will automatically write the M Code for you.


  • Brian

Hey BrianJ

I have used conditional column already for different column but for the dates, I can’t select the operator “is a date”


Sorry - my bad. You will need custom M code for this. I’ll post that for you shortly…

Clearly undercaffeinated this morning. :upside_down_face:

  • Brian


Give this a go:

PerIsDate = if Value.Is([Period], type date) then 1 else 0

Solution file attached.

Thank you @BrianJ the fomrula work but unfortunately I can;t use it.

I am trying to transform the data but I can’t get it right.M code transform.xlsx (16.7 KB)

The result that I want to achieve is:


Where the Account Code has a Star I could drill down the dates down and then filter by star no issue.

The issue that I have is when the data has full information but there is a split

I don’t want to include the first top row but I want to keep the split(ensuring dates are drill down)

Could you please guide me how can I achieve the correct transformation result?

Hi @Matty,

I hope I understood your requirement correctly… here’s what I did.
Turned your data into a table and loaded into Power Query

Filtered out the blank rows

Grouped By the columns Period and Control

Added an Index from 1

Extracted AllRows and added Index.1


Filtered the Query down to rows where the Index = 1

Filled down values in columns: Period, Date and Batch

Filtered out the rows containing a “*”


This staging query is finished, Next for the Duplicate query

Filtered the Query down to rows where the Index <> 1


Next I Apended these Queries into a new Query
And filled dow Period and Date columns

I hope this is helpful.
Here’s your file: M code transform.xlsx (457.1 KB)

Hi @BrianJ

Unfortunately, it’s incorrect as few entries are double-counted. I applied your workings to my file but I literally received the same result which is incorrect.

Raw data:
m code 2.xlsx (720.4 KB)

Excel Formula Total:
In the below file there is a total but also I did few calculations in order to get to 209,132,868.40
Please see Cell Y7 and Y9 sum of them give us the correct results
m code.xlsx (1.3 MB)

While I use your technique and mine I get 216,104,183.73
Transformation.pbix (537.6 KB)

Please let me explain what I want to achieve:

As in your file, I want to drill down period and month for each transaction but I need to exclude transactions that are:

  1. where the row line is with * in Alt account
    Highlighted in yellow values equal to the green. I would like to keep values highlighted in green as they give an alt account also drill down period and dates.

The issue come in point 2

  1. Some of the values are in one line which would be perfect for us but there some values that have a split and total:


Once again I would like to show only value in green.

  1. Keeping any individual entries that are alone:


The issue comes when you need to differentiate between below transactions:

I need to have alt account as I would like to see allocation to each account and also do time intelligence measures.

In the file m code you will see a split for 27milion between each account but I can’t split the 181 million values with a *.

I tried many other techniques in excel but I always came to the same incorrect results.

I don’t know how I can get every individual line for values highlighted in green ensuring not to doublecount values or not to remove any single lines.

I will appreciate your help.

Thank you for your screenshots and all your hard work!

Okay so I misunderstood your requirement, sorry about that, results now match your xlsx

Turned out to be straight forward with the logic from your excel sheet, just added one Custom Column
if [Period] is date and [Acct Code] is text and [Acct Code] <>"" then “Date” else if [Acct Code] ="" then “Astriks” else null

Now the query returned one error, I separated that but you will have to check that

Here’s your file back, if you update the FileLocation parameters, the queries will be restored.
Transformation.pbix (719.9 KB)

I hope this is helpful

1 Like


Thanks very much for jumping in on this one. I’ve been tied up continuously since my initial response to @Matty this morning.

  • Brian

Hi @Mellissa,

Thank you for this.

Apologies, but this is not what I meant.

As I mentioned before the total = 209,132,868.40, your formula and results equal to my results in Excel, but this is not where I stuck. Apologies, maybe I did;t explain it very well.

My issue is with data Transformation.

As I written before the file contains transactions.

There are 3 types of transactions(the number of rows for 1 and 2 can be increased or decreased, its a split of the total main row (highlighted in yellow)as I mentioned in my previous post):


In excel file I wrote many other logic separators to find a way in order to transform data to get to the result below:

(done manually as I can’t find a logic way how can I do it)

Also I can’t use Batch Id as reference as sometimes this data is missing.

@Melissa @BrianJ

If the data can be transfromed to:

Then I am able to do the calculation for each account cost:

The results below are incorrect, but I just stuck with transformation of the data.

@Melissa @BrianJ

For the First logic operator I thought to use

If Period is balnk and alt account is text and alt account <>"*" then I want to keep the data,

but then I don’t know how can I keep the third part a single row transaction ?

Think I’m onto something, I’ll get back to you shortly

Thank @Melissa,

Is it maybe possible to create logic with index column if the total contains only one row and then blank you want to keep this data?

The split will always contain 2 or more rows.

So I think I got your requirement this time :upside_down_face:

I buit on the previous query, so without the error row
Grouped by [Control]

Then (1) check if there are multiple rows inside the nested table
if there are; (2) fill down values and (3) removed the first row
else (4) return the table as is

With this result

Here’s the updated file: Transformation.pbix (850.4 KB)

I hope this is helpful.

1 Like

Thank you @Melissa slowly I am studying your formula to have good understanding of the M code.

I think it worked I get the correct results for Net Gross or VAT amount.

Please allow me to check on smaller data if it will match to excel :slight_smile: ( I believe it does just want check as it was driving me crazy today as I wan;t be able to do it on my own :frowning: )

I will come back to you tomorrow :slight_smile:

Once again thank you!

No worries, let me know if you have any questions.

small tiny question,

I noticed you have added parameter to the file source.

Before you have created the parameter did you first imported source from excel, then added a parameter and then you changed the source meaning replacing it with parameter?

No I created the parameter first and then replaced the hard coded file location in the source step of each of your queries but the order is not that important, although I tend to create my parameters first.