Prepayment PQ - Is it possible?

DAX DAX Calculations
1

Hi DNA Team,

Quite recently I thought about one idea if this is possible to do it in Power Query.

I tried to find something on youtube or google but couldn’t find anything relevant.

I was just wondering as per the attached file is it possible to create something similar in Power Query.

Prepayment.xlsx (13.0 KB)

In Column I to P there is a formula which calculates if the amount should be prepaid or not.

Is it possible to put this in DAX? Is there any formula where you could use a Header of the column in the formula?

I will appreciate your help.

2

Hello @Matty,

Please find the attached PBIX file of my working for the reference.

Just give me half an hour as I’m middle of something. Once I get free I’ll surely come up with a detailed explanation about the transformations that I’ve made.

Till then please check out the file.

Thanks & Warm Regards,
Harsh

Power Query Transformations.pbix (119.9 KB)

2 Likes
3

Hi @Matty,

Here’s another approach.
First calculate the number of months between the dates

image
image1451×169 26 KB

Created a list that contains a value for each month

image
image1455×349 42.4 KB

Extracted that list to new rows and calculated a monthly charge date

image
image1456×256 26.5 KB

You should not be aiming to do DAX calculations across columns but use filters over an unpivoted dataset. I hope this is helpful.

Here’s my sample file.
eDNA - Prepayment PQ.pbix (19.8 KB)

3 Likes
4

Hello @Matty,

Firstly, I would like to apologize as I got caught up in some work and was not able to provide the explanation. So here’s the explanation about the transformations that I’ve made and if you’ve checked the file you might have I converted one table into the two tables i.e. Fact Table and the Dimension Table.

Part 1: Keeping the Imported Data as it is.

In the PBIX file, I’ve kept the original data as it and have created two reference tables from it. One will help me in preparing the Fact Table while the other will help me in preparing the Dimension Table. And after creating the two reference tables from I’ve right - clicked on the original table and unchecked the “Enable Load” option because this table now will act as “Supporting Table” for me. That is I still want the data to be loaded in my PBIX file just don’t want to be loaded in my report.

Part 2: Creating the Fact Table

Now, moving further the first table which I’d referenced will be converted to the Fact Table. Since the data was in the horizontal format my first priority was to convert both these referenced tables into the vertical format.

Step 1: I’ve to remove Top Row (Row No.1) since that was not required for me at all. And just to remind you when you’ll refresh your dataset I won’t show error because we’ve kept the original data as it in our PBIX file and these the referencing tables so no need to worry about that.

Step 2: Promoted my first Row as Headers.

Step 3: Changed the Type and Unpivoted the Date columns which were in the horizontal format.

Step 4: Now, here’s the main part after unpivoting the data if you see figures in the Invoice Amount, Prepaid Amount and Monthly Charge started getting repeated which was wrong. Because this figures were determinable only once and if we load our data here and start making analysis we get utterly wrong figures so I removed those columns from the table. Below is the screenshot provided for the reference -

Power Query Transformation - 1
Power Query Transformation - 11212×260 65 KB

Still I performed two more steps but they’re miscellaneous.

Part 3: Creating the Dimension Table

Now since I’d deleted those 3 columns we need to bring those 3 columns in our analysis in the form of Dimension Table and transform our data in such a way that this figures appear only once since they’re determined at the start of the period. So all the transformation steps remain the same only the change which I made in this case was to retain this 3 columns and delete the other remaining columns.

Part 4: Creating Date Table

Lastly, since all our analysis is based on the date itself, I created the date table. And now, we’ll click on the “Close & Apply Button” and now to the “Modelling View”.

Now, since you’ve 3 date columns in your table i.e. Month, Start and End. I’ve created inactive relationships with the Date table and between Fact and Dimension table one column is common and that is Details so I created one active relationship there.

So now, the end result should look like as per the screenshot provided below -

Power Query Transformation
Power Query Transformation700×656 34.4 KB

Now, to make this relationship active you can definitely use the USERLELATIONSHIP() function and carry out or perform the analysis as desired.

I know the explanation got bigger but hoping you’ll find this useful and helps you in your analysis :slightly_smiling_face:. I’m also attaching the the Excel as well as PBIX file for the reference.

Thanks & Warm Regards,
Harsh

Prepayment.xlsx (13.0 KB)

Power Query Transformations.pbix (114.2 KB)

2 Likes
5

Hi @Melissa,

Thank you.

I just reviewed your file. You have used M code :slight_smile: Thank you.

In the first step where you are calculating Month the if statement is not necessary it should always add 1, if the post month relates to the period of prepayment.

Your formula is correct, but the only issue is that I have is the last monthly charge should be slightly different 1182.84.

There could be another scenario where I could have another column Post Month = 06/2020, then
the result for may should be 0 and June should be 2365.66, all other moths remains the same.

It’s much easier to do it in Excel, but I just wonder if this is possible in PQ.

6

Hi @Matty,

Yes it most certainly is, now we’re calculating from the date in the [Start] column but you could override that with a [Post Month] column if that contains a value. That wouldn’t be that hard actually…

7

Hi @Melissa,

Addidng extra column with post month and changing the M code is not hard as you wrote, I am not experience with M Code but I can use your code as base and just modify.

My issue is how can I tell in PQ to change the numbers?

I looked at Microsoft website and I though I could use:

https://docs.microsoft.com/en-us/powerquery-m/list-max ,as the last month often will need to be adjusted, but I received an error:
image

Apologies,

but for me is simple to do it in Excel base on date/ ifs statements logic, whereas in PQ I understand that the calcualtions are done on row level.

8

@Hi Melisa,

I just added two more columns:

image
image1689×628 34.4 KB

Then its just about clean up, is there any other approach or would you say that is okay?

9

Excellent job @Matty :+1:

But instead of “else 0” in your Custom column, I would say “else [Monthly Charge]”

BTW you can also easily change the [Monthly Charge Date] to reflect the end of the Month, by wrapping Date.EndOfMonth around it.

10

Thank you @Melissa,

I will try to do it now with other scenario with extra date columnt of post month :slight_smile:

Can you recommend any book or anything regarding M-Code functions or just read through via Microsoft web?

11

We’ve actually devoted a topic to that :wink:

.

1 Like
12

Thank you @Melissa,

I will read it through tomorrow :slight_smile:

There is soo much to learn, unbelievable … :slight_smile:

I wish to soak all this knowledge like a sponge Power BI, Macro, M code, SQL, Python and R + :smiley:

@Harsh Thank you for your response and all the effort :slight_smile:

13

Amazing support Harsh, well done

14

The PQ master!