How to generate queries in Query Editor with M Code

[deltaselect]

Good evening,

See attached PBIX, a dummy file of invoices. (Invoicenr, Invoice value, Months of revenue, Date, Client), with invoices having more than one month revenue in the future. For example, if Months of Revenue is 12, it means that the revenue is to be split over the future 12 months; when 1, the revenue belongs to the current month. (The revenue of the future months is calculated as Invoice amount / Months of revenue)

1. I would like to have a query which creates a revenue fact table from the invoice fact table, with Revenue Date per month, Client, Revenue per month and Invoice number. This query should also work for renewed data.
   I received already some code, but regrettably have too much problems with the M code syntax to get it to work, could anyone help me out ?

MRR - invoices split into period revenue.pbix (125.5 KB)

2. Another solved topic on the forum was about some wrong double sales order data, which needed to be cleaned up, which was completely solved with a kind of M-code query. Could anyone provide me of some links where is explained how to develop such queries ? (preferably explained in the simplest possible way) ?

Thanks in advance !

[BrianJ]

@deltaselect,

You’re in luck. Enterprise DNA Expert @haroonali1000 led a Problem of the Week earlier this year focused on this exact issue. If you go through this thread, it will have his writeup, his solution and solution video, as well as a bunch of other solutions (with PBIX files) from other EDNA members and experts. I’m confident that these materials should have all the info necessary to meet your requirement, but if you still run into any problems, pl;ease just post an updated work-in-progress PBIX file and highlight where you got stuck.

Problem of the Week #4 (PQ) - Addressing Granularity Issues

From @haroonali1000: OK, here we go! Problem of the Week #4 is now live! For background on this initiative, check out this post and this week’s launch video that Brian and I did on YouTube. Problem of the week #4 First Challenges and now problem of the week!! Don’t worry it’s just a quick guest appearance. So this week’s problem of the week was something I was tackling back in November and while chatting it through with the Oracle (@BrianJ) he said it would make for a great problem of the…

Also, here are a couple of videos responsive to the second part of your question. For learning M, I cannot recommend EDNA Expert @Melissa’s videos highly enough - I think they are truly the best resource on the internet for learning Power Query and M code:

How To Remove Duplicate Rows In Power Query Based On Conditions

Creating A Custom Text Cleaning Function In Power Query

Another great resource is Gil Raviv’s book on Power Query and M, which I think is by far the best book available on this topic. It has an excellent introducton to writing M code.

Finally, here’s a video I did recently that provides an introduction to the “Learn by Googling” appproach to M code that I learned from Melissa and have found enormously helpful:

Incorporating M In Custom Column - Problem Of The Week #9 (Power Query Solution)

I hope this is helpful.

• Brian

[deltaselect]

See attached screenshot and PBIX file, today learnt some about M-code, but missing “M-code branching” information, how the codes are to combined.

Problem : how to extend invoice data into revenue period data with M-code ? The invoiced amount need to be split into future revenue periods in months.

Not able to code it, even with the code-examples with solutions of problem of the week 4.

Thanks in advance for your help !

MRR - invoices split into period revenue2604.pbix (136.2 KB)

[Melissa]

Hi @deltaselect,

Give this a go, just paste it in a new blank query in your PBIX file.

let
    Source = Invoiced,
    CreateDates = Table.AddColumn(Source, "Invoice date", each let myDate = [Date] in if ([Months]) >1 then List.Transform( {0..([Months]-1)}, each try Date.AddMonths( myDate, _ ) otherwise Date.EndOfMonth( Date.AddMonths( #date( Date.Year(myDate), Date.Month(myDate), 1), _ ))) else {[Date]} ),
    ExpandDates = Table.ExpandListColumn(CreateDates, "Invoice date"),
    AddValue = Table.AddColumn(ExpandDates, "Invoice value", each [Invoiced] / [Months])
in
    AddValue

.
First create an item for each Month using the list initializer { }

.
Built on this logic

• First get the “Date” from the current record.
• Test if there are rows to add
• Transform the list items into a date, not all months have the same number of days so if that returns an invalid date, calculate the last day of the month instead.
• If there are no rows to add, get the Date as a list

I hope this is helpful.

[EnterpriseDNA]

It’s great to know that you are making progress with your query @deltaselect. Please don’t forget if your question has been answered within the forum it is important to mark your thread as ‘solved’. Also, we’ve recently launched the Enterprise DNA Forum User Experience Survey, please feel free to answer it and give your insights on how we can further improve the Support forum. Thanks!

[deltaselect]

Hi Melissa,
Thanks a lot, this is exactly what I was looking for ! And it works, as you know, perfectly !

======
Other questions which appears with me:

1. Is it true that to report monthly revenue and invoicing periods in the right sequence, you need to create an unique period table with an sequence index, (as you can not sort monthly periods in the date table) ?
2. See below screenshot, how to avoid irrelevant cumulatives ? Adjust the DAX formula of cumulative revenue ? (Filtering out months is not dynamic)

Attached also the PBIX file, thanks again for all the support.

MRR - invoices split into period revenue27042021.pbix (129.2 KB)

[Melissa]

Hi @deltaselect,

I amended your Date table and kept appropriate sort by columns. This removes the need for your period table.

.

And to stop your Cum calc from projecting forward you can add an IF statement, like so

Cum. Revenue = 
IF( NOT( ISBLANK( [Total Revenue] )),
    CALCULATE( [Total Revenue],
        FILTER( ALLSELECTED( DateT ),
            DateT[Date] <= MAX( DateT[Date] )
        )
    )
)

.

Here’s your updated PBIX. MRR - invoices split into period revenue27042021.pbix (115.8 KB)
I hope this is helpful.

[deltaselect]

Thanks again, Melissa ! learned another 2 things ! (DAX und column sorting for visuals)

It sounds bit weird to me that column Year Month can not be sorted by the date, as the date seems to have, like an index, the perfect sequence in this case.
I now understand from you, that you can sort the column Year Month for the visuals by selecting MonthnYear, a many by many selection that works.
It works perfect, so I will use it !

[Melissa]

Hi @deltaselect,

Yes the sort by column needs to be at the same granularity as the attribute.
Glad I could help.

All the best.

[BrianJ]

@deltaselect,

Along these lines, one thing you might find helpful is a “cheat sheet” that I developed to accompany Melissa’s awesome Extended Date Table. One of the things it provides is guidance for each field in the date table, what other field should be used to sort it.

Here’s the thread where you can obtain the cheat sheet free from Enterprise DNA:

New Cheat Sheet for Extended Date Table

Extended Date Table Fans, I’m pleased to announce the release of the new deluxe version of the Extended Date Table Cheat Sheet, an essential accompaniment to @Melissa’s awesome and recently revised Extended Date Table code. Click here to obtain the new version, which now includes not only all the field names and examples for each, but field type and information on how to sort every field in the table. We will update this with every major update @Melissa makes to the date table code. We hope…

I hope this is helpful

• Brian

[deltaselect]

Hi Brian, sorry, but I am missing the link, If I click Here, I can not download the New Cheat Sheet, or do I oversee something ?

[BrianJ]

@deltaselect,

Enterprise DNA uses a number of free resources as marketing leads (the DAX Reference Guide, the DAX Optimization Guide, The Extended Date Table Cheat Sheet, etc.). Just click on the link below, scroll down to the Cheat Sheet section, click the button and enter your email address and they will automatically email you a copy of the cheat sheet.

Power BI Resources & Training Material | Enterprise DNA

– Brian