cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
heytherejem Member
Member

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 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. Sample.PNG

 

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Using M Query for an 'if else' argument

Hi @heytherejem 

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

Capture17.JPGCapture18.JPG

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.

View solution in original post

5 REPLIES 5
Washivale Member
Member

Re: Using M Query for an 'if else' argument

Hello @heytherejem ,

 

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

 

 

heytherejem Member
Member

Re: Using M Query for an 'if else' argument

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. 

 

Sample.PNGRate TableCustom Column.PNGMapping.PNGMapping Table

 

 

Thanks so much for helping me with this 🙂

Washivale Member
Member

Re: Using M Query for an 'if else' argument

Hi @heytherejem ,

 

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

heytherejem Member
Member

Re: Using M Query for an 'if else' argument

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:

 

Example1.PNGTHE RESULTFormula.PNGMY RATE CUSTOM COLUMNMappingTable.PNGTHE MAPPING TABLE WITH DATES

Community Support Team
Community Support Team

Re: Using M Query for an 'if else' argument

Hi @heytherejem 

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

Capture17.JPGCapture18.JPG

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.

View solution in original post

Helpful resources

Announcements
Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 6 members 2,995 guests
Please welcome our newest community members: