Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Using M Query for an 'if else' argument

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 hav...
Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    6 years ago

    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's avatar
Washivale
Resolver V
6 years ago

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's avatar
    Anonymous
    Not applicable
    6 years ago

    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's avatar
      Washivale
      Resolver V
      6 years ago

      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's avatar
        Anonymous
        Not applicable
        6 years ago

        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