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 CodeNominal NameGroupAmountReporting TypeMonthMonth Movement4000HatsCommercial Sales600Actual31/10/20xx1504000HatsCommercial Sales450Actual30/09/20xx2504000HatsCommercial Sales200Actual31/08/20xx Power Query

Frequent Visitor

1 year ago

Solved

Power Query Formula

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

General Comment

Need Help

Anonymous

1 year ago

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.

Power Query Formula.pbix41 KB

Omid_Motamedise

1 year ago

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"

7 Replies

Anonymous

Not applicable

1 year ago

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.

Power Query Formula.pbix41 KB

Omid_Motamedise

Super User

1 year ago

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"

shafiz_p

Super User

1 year ago

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

Ahmedx
Super User
1 year ago
pls try this

777.pbix21 KB
- deblacus
  Frequent Visitor
  1 year ago
  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
    Super User
    1 year ago
    pls share data and show expected result
juju63
Regular Visitor
1 year ago
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

Forum Discussion

Power Query Formula

7 Replies

Related content

Divide formula in power query

Lookup Formula in Power Query

If formula

Power Query dates

formula