Using M Query for an 'if else' argument

[Anonymous]

I have a dataset for invoice billing. 

I have a column called Volume which is a numerical value (in the invoice table)

I have a column called Rate which is a financial value  (in rate table)

I have a date column in the invoice table, and a date column in the rate table which relates to when the rate is applicable from.

Both tables have a unique key which can be used as a common dimension

There can be more than one rate per unique key depending on the date the rate is applicable from.

For example in January, the rate can be $1.01 and in February the rate can be $1.02

I need to multiply Rate by Volume to get the actual expense - however, I need to make sure I apply the correct Rate. 

If the Rate Date is the same or less than the Invoice Date, then multiply by Volume, else null. 

I have no idea how to do this! I have attached an image to show what I want to happen. 

[v-juanli-msft · Accepted solution]

Hi @Anonymous 

You can download my file and click on the steps on the right step pane to see what is applied step by step.

Code in Advanced editor

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTLUN9Q3MjC0BDKNzA0MlGJ1YOJGOMSNcYiboIrHAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [uniquekey = _t, #"invoice period" = _t, volume = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"uniquekey", Int64.Type}, {"invoice period", type date}, {"volume", Int64.Type}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1),
    #"Merged Queries" = Table.NestedJoin(#"Added Index", {"uniquekey"}, rate, {"uniquekey"}, "rate", JoinKind.LeftOuter),
    #"Expanded rate" = Table.ExpandTableColumn(#"Merged Queries", "rate", {"uniquekey", "rateperiod", "rate"}, {"rate.uniquekey", "rate.rateperiod", "rate.rate"}),
    #"Added Conditional Column" = Table.AddColumn(#"Expanded rate", "condition1", each if [invoice period] >= [rate.rateperiod] then [rate.rate] else null),
    #"Filtered Rows" = Table.SelectRows(#"Added Conditional Column", each [condition1] <> null and [condition1] <> ""),
    #"Grouped Rows" = Table.Group(#"Filtered Rows", {"Index"}, {{"Count", each _, type table [uniquekey=number, invoice period=date, volume=number, Index=number, rate.uniquekey=number, rate.rateperiod=date, rate.rate=number, condition1=number]}}),
    #"Aggregated Count" = Table.AggregateTableColumn(#"Grouped Rows", "Count", {{"rate.rateperiod", List.Max, "Max of Count.rate.rateperiod"}}),
    #"Merged Queries1" = Table.NestedJoin(#"Filtered Rows", {"Index"}, #"Aggregated Count", {"Index"}, "Query1", JoinKind.LeftOuter),
    #"Expanded Query1" = Table.ExpandTableColumn(#"Merged Queries1", "Query1", {"Max of Count.rate.rateperiod"}, {"Query1.Max of Count.rate.rateperiod"}),
    #"Added Conditional Column1" = Table.AddColumn(#"Expanded Query1", "condition 2", each if [rate.rateperiod] = [Query1.Max of Count.rate.rateperiod] then [rate.rate] else null),
    #"Filtered Rows1" = Table.SelectRows(#"Added Conditional Column1", each [condition 2] <> null and [condition 2] <> ""),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows1",{"rate.uniquekey", "rate.rateperiod", "rate.rate", "condition1", "Query1.Max of Count.rate.rateperiod"}),
    #"Added Custom" = Table.AddColumn(#"Removed Columns", "new column", each [volume]*[condition 2])
in
    #"Added Custom"

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Washivale]

Hello @Anonymous ,

I am not sure how best we can use if else in this scenario, however, i can suggest an approach that might help you to resolve your question.

Step1. Merge Invoice Table with Rate table on UniqueKey

Step2. Add a Custom Column with following function

let rper = [InvoicePeriod] in Table.SelectRows([RateTable], each [RatePeriod] >= rper)

Step3. Expand table columns to show you rate in next column

Step4. Use expanded column as multiplier for volume

Thank You,

Washivale

[Anonymous]

Hi @Washivale 

This so nearly almost worked perfectly. However, it's not pulling through the correct rates based on date. It actually looks completely random to me how it's chosen the rate!

I've attached what it's pulling through.

What I want is for the 1.57 rate to be applied 01/01/2019 through to 01/03/2019 and the 1.89 rate be pulled through from 01/04/2019 onwards. 

In the Mapping table, the rate 1.57 has a [RatePeriod] of 01/01/2019 and the rate 1.89 has a [RatePeriod] of 01/04/2019. 

I've attached this as well. 

Rate TableMapping Table

Thanks so much for helping me with this 🙂

[Washivale]

Hi @Anonymous ,

You might want to tweak data model a bit on RateTable, to include one more column indicating end of RatePeriod, using that you can add one more condition to existing custom column to filter rows based on the Start and End of RatePeriod. something like below:

each RatePeriodEnd <= rper and RatePeriodStart <= rper)

give it a try

Thank You,

Washivale

[Anonymous]

Hi @Washivale 

I think I must be doing something wrong because the logic seems perfect to me, I've done as you suggested and created an [EndofRatePeriod] column. This is the last month the rate should be applied. The [RatePeriod] column should be the first month the rate should be applied.

Therefore, if the [InvoicePeriod] is less than or equal to [EndofRatePeriod] and greater than or equal to [RatePeriod], apply the rate. 

See my screenshots showing what is happening:

THE RESULTMY RATE CUSTOM COLUMNTHE MAPPING TABLE WITH DATES