Power Query Formula

[deblacus]

Hi,

I'm looking for a power query m function to insert a new month column and calculate the variance between one month to previous month i.e. oct ytd - sep ytd = oct mtd etc.   based on the data shown below produced in excel.

many thanks in advance

NL Code	Nominal Name	Group	Amount	Reporting Type	Month	Month Movement
4000	Hats	Commercial Sales	600	Actual	31/10/20xx	150
4000	Hats	Commercial Sales	450	Actual	30/09/20xx	250
4000	Hats	Commercial Sales	200	Actual	31/08/20xx

Power Query

[v-junyant-msft · Accepted solution]

Hi @deblacus ,

Thanks for all the replies!

And @deblacus , I have expanded the sample data according to your needs. Please check whether this is the result you want:

let
    Source = Table.FromRows({
        {4000, "Hats", "Commercial Sales", 600, "Actual", "10/31/2024"},
        {4000, "Hats", "Commercial Sales", 450, "Actual", "9/30/2024"},
        {4000, "Hats", "Commercial Sales", 200, "Actual", "8/31/2024"},
        {4001, "Scarves", "Commercial Sales", 300, "Actual", "10/31/2024"},
        {4001, "Scarves", "Commercial Sales", 400, "Actual", "9/30/2024"},
        {4001, "Scarves", "Commercial Sales", 500, "Actual", "8/31/2024"},
        {4002, "Gloves", "Commercial Sales", 150, "Actual", "10/31/2024"},
        {4002, "Gloves", "Commercial Sales", 100, "Actual", "9/30/2024"},
        {4002, "Gloves", "Commercial Sales", 75, "Actual", "8/31/2024"}
    }, {"NL Code", "Nominal Name", "Group", "Amount", "Reporting Type", "Month"}),
    ChangedType = Table.TransformColumnTypes(Source, {{"Month", type date}}),
    SortedTable = Table.Sort(ChangedType, {{"NL Code", Order.Ascending}, {"Month", Order.Descending}}),
    AddedIndex = Table.AddIndexColumn(SortedTable, "Index", 1, 1, Int64.Type),
    AddMonthMovement = Table.AddColumn(AddedIndex, "Month Movement", each let
            CurrentIndex = [Index],
            PreviousRow = try AddedIndex{CurrentIndex - 2} otherwise null,
            PreviousAmount = if PreviousRow <> null and [NL Code] = PreviousRow[NL Code] then PreviousRow[Amount] else null
        in
            if PreviousAmount <> null then PreviousAmount - [Amount] else null),
    RemovedIndex = Table.RemoveColumns(AddMonthMovement, {"Index"})
in
    RemovedIndex

And the final output is as below:

Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Omid_Motamedise · Accepted solution]

Copy the following code and past it into Advance Editor to see the result

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjEwMFDSUfJILCkGUs75ubmpRcmZiTkKwYk5qSAhM7C8Y3JJaWIOkGFsqG9ooG9kYGSkFKtDhHYTU1TtBvoGliRoN8Kw3cACqj0WAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"NL Code" = _t, #"Nominal Name" = _t, Group = _t, Amount = _t, #"Reporting Type" = _t, Month = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"NL Code", Int64.Type}, {"Nominal Name", type text}, {"Group", type text}, {"Amount", Int64.Type}, {"Reporting Type", type text}, {"Month", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each try [Amount]-Table.Max(Table.SelectRows(#"Changed Type", (x)=> x[Month]<[Month]),"Month")[Amount] otherwise "")
in
    #"Added Custom"

[v-junyant-msft · Accepted solution]

Hi @deblacus ,

Thanks for all the replies!

And @deblacus , I have expanded the sample data according to your needs. Please check whether this is the result you want:

let
    Source = Table.FromRows({
        {4000, "Hats", "Commercial Sales", 600, "Actual", "10/31/2024"},
        {4000, "Hats", "Commercial Sales", 450, "Actual", "9/30/2024"},
        {4000, "Hats", "Commercial Sales", 200, "Actual", "8/31/2024"},
        {4001, "Scarves", "Commercial Sales", 300, "Actual", "10/31/2024"},
        {4001, "Scarves", "Commercial Sales", 400, "Actual", "9/30/2024"},
        {4001, "Scarves", "Commercial Sales", 500, "Actual", "8/31/2024"},
        {4002, "Gloves", "Commercial Sales", 150, "Actual", "10/31/2024"},
        {4002, "Gloves", "Commercial Sales", 100, "Actual", "9/30/2024"},
        {4002, "Gloves", "Commercial Sales", 75, "Actual", "8/31/2024"}
    }, {"NL Code", "Nominal Name", "Group", "Amount", "Reporting Type", "Month"}),
    ChangedType = Table.TransformColumnTypes(Source, {{"Month", type date}}),
    SortedTable = Table.Sort(ChangedType, {{"NL Code", Order.Ascending}, {"Month", Order.Descending}}),
    AddedIndex = Table.AddIndexColumn(SortedTable, "Index", 1, 1, Int64.Type),
    AddMonthMovement = Table.AddColumn(AddedIndex, "Month Movement", each let
            CurrentIndex = [Index],
            PreviousRow = try AddedIndex{CurrentIndex - 2} otherwise null,
            PreviousAmount = if PreviousRow <> null and [NL Code] = PreviousRow[NL Code] then PreviousRow[Amount] else null
        in
            if PreviousAmount <> null then PreviousAmount - [Amount] else null),
    RemovedIndex = Table.RemoveColumns(AddMonthMovement, {"Index"})
in
    RemovedIndex

And the final output is as below:

Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[juju63]

hello deblacus

if I well understood your need, why not to use index 0 & index 1 columns ?

just add 2 columns index 0 & index 1 then merge the query with itself on those columns and just select the wanted column.

this way you can have month value & month +/-1 value and make any needed calculations

hopefuly this can help you 

[Ahmedx]

pls try this

[deblacus]

Many thanks for the replies, I meant to say I want to apply a variance between all rows not just the three shown.

so basically I want to see the month movement in a each product/line item.   

Hope I explained myself better !

[Ahmedx]

pls share data and show expected result

[shafiz_p]

Hi @deblacus 
Create function by using the below M code:

let
    CalculateVariance = (inputTable as table) as table =>
    let
        #"Changed Type with Locale" = Table.TransformColumnTypes(inputTable, {{"Month", type date}}, "en-GB"),
        #"Changed Type" = Table.TransformColumnTypes(#"Changed Type with Locale",{{"Amount", Int64.Type}}),
        #"Inserted Month" = Table.AddColumn(#"Changed Type", "Month.1", each Date.Month([Month]), Int64.Type),
        #"Sorted Rows" = Table.Sort(#"Inserted Month",{{"NL Code", Order.Ascending}, {"Month", Order.Descending}}),
        #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 1, 1, Int64.Type),
        #"Added Previous Amount" = Table.AddColumn(#"Added Index", "Previous Month Amount", each try if [Index] < Table.RowCount(#"Added Index") and [NL Code] = #"Added Index"{[Index]}[NL Code] then #"Added Index"{[Index]}[Amount] else null otherwise null, type number),
        #"Added Variance" = Table.AddColumn(#"Added Previous Amount", "Variance", each [Amount] - [Previous Month Amount], type number),
        #"Removed Unnecessary Columns" = Table.SelectColumns(#"Added Variance", Table.ColumnNames(inputTable) & {"Variance"})
    in
        #"Removed Unnecessary Columns"
in
    CalculateVariance

Rename function to Variance. See image below:

Now call this function in your source query. See image:

You are done. 

You can also achieve this using the self merging technique. Need only 2 index column : First one is starting from 1 and second one is starting from 0. 

Now merge Index from 1 with the index from 0 and expand one column amount and substract.


Hope this helps!!

If this solved your problem, please accept it as a solution and a kudos!!

Best Regards,
Shahariar Hafiz