Price Range - PowerPivot

[tusharmehta]

Hello Everyone,

I herewith posting one example in which I have two tables:

1 Transaction table
2 Range Table

[TABLE="class: grid, width: 512"]
<tbody>[TR]
[TD="width: 256, colspan: 4, align: center"]Transaction Table[/TD]
[TD="width: 64, align: center"][/TD]
[TD="width: 192, colspan: 3, align: center"]Range Table[/TD]
[/TR]
[TR]
[TD="align: center"]Sr. No[/TD]
[TD="align: center"]Product[/TD]
[TD="align: center"]Amount[/TD]
[TD="align: center"]Range[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Range[/TD]
[TD="align: center"]Start[/TD]
[TD="align: center"]End[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]Pr1[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]??[/TD]
[TD="align: right"][/TD]
[TD="align: right"]0-100[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]100[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]Pr2[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]??[/TD]
[TD="align: right"][/TD]
[TD="align: right"]101-200[/TD]
[TD="align: right"]101[/TD]
[TD="align: right"]200[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]Pr3[/TD]
[TD="align: right"]150[/TD]
[TD="align: right"]??[/TD]
[TD="align: right"][/TD]
[TD="align: right"]201-300[/TD]
[TD="align: right"]201[/TD]
[TD="align: right"]300[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]Pr4[/TD]
[TD="align: right"]110[/TD]
[TD="align: right"]??[/TD]
[TD="align: right"][/TD]
[TD="align: right"]301-400[/TD]
[TD="align: right"]301[/TD]
[TD="align: right"]400[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD="align: right"]Pr5[/TD]
[TD="align: right"]300[/TD]
[TD="align: right"]??[/TD]
[TD="align: right"][/TD]
[TD="align: right"]401-500[/TD]
[TD="align: right"]401[/TD]
[TD="align: right"]500[/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD="align: right"]Pr6[/TD]
[TD="align: right"]450[/TD]
[TD="align: right"]??[/TD]
[TD="align: right"][/TD]
[TD="align: right"]501-600[/TD]
[TD="align: right"]501[/TD]
[TD="align: right"]600[/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD="align: right"]Pr7[/TD]
[TD="align: right"]122[/TD]
[TD="align: right"]??[/TD]
[TD="align: right"][/TD]
[TD="align: right"]601-700[/TD]
[TD="align: right"]601[/TD]
[TD="align: right"]700[/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD="align: right"]Pr8[/TD]
[TD="align: right"]1000[/TD]
[TD="align: right"]??[/TD]
[TD="align: right"][/TD]
[TD="align: right"]701-800[/TD]
[TD="align: right"]701[/TD]
[TD="align: right"]800[/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD="align: right"]Pr9[/TD]
[TD="align: right"]232[/TD]
[TD="align: right"]??[/TD]
[TD="align: right"][/TD]
[TD="align: right"]801-900[/TD]
[TD="align: right"]801[/TD]
[TD="align: right"]900[/TD]
[/TR]
[TR]
[TD="align: right"]10[/TD]
[TD="align: right"]Pr10[/TD]
[TD="align: right"]265[/TD]
[TD="align: right"]??[/TD]
[TD="align: right"][/TD]
[TD="align: right"]901-1000[/TD]
[TD="align: right"]901[/TD]
[TD="align: right"]1000[/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1001-1100[/TD]
[TD="align: right"]1001[/TD]
[TD="align: right"]1100[/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1101-1200[/TD]
[TD="align: right"]1101[/TD]
[TD="align: right"]1200[/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1201-1300[/TD]
[TD="align: right"]1201[/TD]
[TD="align: right"]1300[/TD]
[/TR]
</tbody>[/TABLE]

My questions is how to fill the Range from Range table to Transaction table automatically using PowerPivot calculate or other functions?

Thanks in Advance.

 

[Hashiru]

Hi Tusharmehta,

I have a alternative Power Query solution to your problem. Here are the steps

1. Load the Transaction table to Power Query.
a. Create a Blank Query using formula:
=Excel.CurrentWorkbook() or
b. Create new workbook and import the Transaction table to Power Query

2. Insert a Modulo with Amount of 100 using the formula:
=Table.AddColumn(#"Expanded Content", "Modulo", each Number.Mod([Amount], 100), type number)

3. Insert an Integer-Division with Amount of 100 using the formula:
=Table.AddColumn(#"Expanded Content", "Modulo", each Number.Mod([Amount], 100), type number)

4. Create a Lower band with the formula:
=Table.AddColumn(#"Expanded Content", "Modulo", each Number.Mod([Amount], 100), type number)

5. Create Upper band with the formular:
= Table.AddColumn(#"Lower Band", "Upper Band", each if [Modulo] > 0 then ([#"Integer-Division"]+1)*100 else [#"Integer-Division"]*100)

6. Create your band with the formular:
= Table.AddColumn(#"Upper Band", "Band", each Text.From([Lower Band]) & "-" & Text.From([Upper Band]))

Hope this helps