PQ merge comission data within a range of numbers

[DataHero]

Hi forum,

Im facing a challenge to solve regarding the calculation of sales comissions.

The comission is depending on a discount for sold items.

I have 2 tables: 

1. DimComission

This table contains the [DiscountUpperBoundary%] & [Comission%].

[DiscountUpperBoundary%]             [Comission%]

0%	10%
3%	10%
5%	10%
10%	10%
15%	9%
17%	8%
20%	6%
25%	4%
30%	0%

As you can see, the more discount a salesman grants to the customer, the less comission the salesman gets.

2. FactComission

This table containts the real sales data from an ERP system for a lot of orders.

This is just a unique extracts of them:

[ActualDiscount%]

0,00 %
1,00 %
5,00 %
6,00 %
10,00 %
12,00 %
14,50 %
15,40 %
16,00 %
18,00 %
19,00 %
20,00 %
23,00 %
23,50 %
25,00 %
26,00 %
28,00 %
29,00 %
30,00 %
32,50 %

My question: How can i merge the Fact table with the ranges of the Dimensions dynamically?

For example, [ActualDiscount%] 6,00 % is not in the DimComission table so 10% is to be taken, as its the next highest [DiscountUpperBoundary%]. 

Could this be solved maybe with a list in PQ or are there other solutions?

Thanks for any help and advice

Kind regards,
DataHero

[slorin]

Hi

Add a new column in FactComission

try DimComission[#"Comission%"]{List.PositionOf(DimComission[#"DiscountUpperBoundary%"],[#"ActualDiscount%"],1,(x,y)=>x<y)+1} otherwise 0

Stéphane

[DataHero]

Hi @slorin  can you explain more about this solution?
What is it doing specifically?

[tackytechtom]

Hi @DataHero ,

Have a look into this blog post. I think they did something similar there:

Merging with date range using Power Query - Exceed

Let me know if this helps 🙂

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/

[DataHero]

Hi @tackytechtom thank you very much for the link.

I tried that solution and it works partially.

I think the problem is that I dont have ranges in the DimComission table.

There is the upper value only but nothing like 0-3%.
And its very time consuming to change this manually in the data source.

Do you know, how I can implement a proper range with 2 columns in Power Query?

Kind regards,
DataHero

[tackytechtom · Accepted solution]

Hi @DataHero ,

Yes, I think you first need to get the two columns DiscountLowerBoundary and DiscountUpperBoundary in your DimComission table:

Here the M code on how to do this:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlBV0lEyBJKxOtFKxsgcU2SOIYo6Q7CcJZRjDuJYQDhGYHVmUA5YmQnUbLAMyIBYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [DiscountUpperBoundary = _t, Comission = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"DiscountUpperBoundary", Percentage.Type}, {"Comission", Percentage.Type}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"DiscountUpperBoundary", "DiscountLowerBoundary"}}),
    #"Added Index" = Table.AddIndexColumn(#"Renamed Columns", "Index", 0, 1, Int64.Type),
    #"Added Index1" = Table.AddIndexColumn(#"Added Index", "Index.1", 1, 1, Int64.Type),
    #"Merged Queries" = Table.NestedJoin(#"Added Index1", {"Index.1"}, #"Added Index1", {"Index"}, "Added Index1", JoinKind.LeftOuter),
    #"Expanded Added Index1" = Table.ExpandTableColumn(#"Merged Queries", "Added Index1", {"DiscountLowerBoundary"}, {"DiscountUpperBoundary"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Added Index1",{"Index", "Index.1"})
in
    #"Removed Columns"

With them in place, you should be able to get the matches as described in the link:

Here the M code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Tc7BCcBACETRXoTcgphxDUktsv23kUPQ8fZA/ZgppmaH7DPlakXr5nQsglwaxdBVHGcP+TbBGHyyYuALYAyMgTFnzPEX9gc=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ActualDiscount = _t]),
    #"Changed Type1" = Table.TransformColumnTypes(Source,{{"ActualDiscount", Percentage.Type}}),
    Merge = Table.AddColumn(#"Changed Type1", "Comission",
                            (F) =>  Table.SelectRows(DimComission,
                                                    (D)=> D[DiscountUpperBoundary] > F[ActualDiscount] and D[DiscountLowerBoundary] <= F[ActualDiscount] )
                           ),
    #"Expanded Comission" = Table.ExpandTableColumn(Merge, "Comission", {"Comission"}, {"Comission"}),
    #"Replaced Value" = Table.ReplaceValue(#"Expanded Comission",null,0,Replacer.ReplaceValue,{"Comission"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Replaced Value",{{"Comission", Percentage.Type}})
in
    #"Changed Type"

Was it this you were looking for? 🙂

Let me know!

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/

[DataHero]

This is really impressive!
Thank you so much for your help!

Kind regards,
DataHero