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.
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.
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. 
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
=== AT THIS POINT I DUPLICATED THE QUERY ===
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:
The issue come in point 2
Once again I would like to show only value in green.
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
Thanks very much for jumping in on this one. I’ve been tied up continuously since my initial response to @Matty this morning.
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.
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.
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
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.
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 
( 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 
)
I will come back to you tomorrow
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.
