Adding column with banding and m

[AndréN]

Hi everybody.

I'm new on PowerBi.

For a few days i try to make a banding but into power query and not with DAX.

I explain myself

I have 2 tables :

 - First one called Criteria with  Label, LowerLimit, UpperLimit

 - Second one called Accounts with AccountNumber, AccountName

Here an extract of my code into PowerBi (that does not work !!)

let
    Criteria = #table({"Label", "LowerLimit" , "UpperLimit"}, {{"Goods solds", 70700000 , 70799999}, {"Other sales", 70800000 , 70899999}, {"Services", 70500000, 70699999}}),
    Accounts = #table({"AccountNumber", "AccountName"}, {{7070100, "Local sales"}, {70800070, "Ancilary Sales"}, {70600000, "Services in Europe"}, {70610000, "International services"}, {70590000, "Local services"}, {70710000, "International Sales"}}),


    Lookup = Table.AddColumn(Accounts, "Lookup", each Table.SelectRows(Criteria, each [AccountNumber]>= [LowerLimit]))),
    // this doesn't work, it returns no rows from the SelectRows funtion.


    // And after making an expand showing the Label from table Criteria
in
    Lookup

I have found a DAX expression using CALCULATE and FILTERS but in criteria there are other columns and i have to create a relation between table Criteria and Account. When i do that i have a warning message that said i couldn't do that because of redundancy problems.

So i try to find a way to do it in M. And i find the challenge interesting and i have all my transformations of accounts into a single screen.

I find this solution https://community.powerbi.com/t5/Desktop/Complex-PowerQuery-Merge-query-using-substrings/m-p/45455#M... from @ImkeF but it use text comparaison and i want it on number.

The goal, if possible, is to compare each AccountNumber with the lower and the upper bound so it will retrurn only one row and putting the label into this new column.

If someone has a suggestion, i couldn't find a solution of this problem into this forum.

Thanks in advance.

[ImkeF]

Hi
If I'm not mistaken, this could be the solution https://youtu.be/EYgKciBr_dg

[AndréN · Accepted solution]

Hi @ImkeF.

Thanks for this answer but it isn't what I need.

Thanks for your answer @Greg_Deckler 

After some hours I find a solution that suit me well. If i could have your opinion and if you know another maner to do that (always without DAX ... 😉 )

You could past this in a new query to see what i intend to do. 

let
    Criteria = #table({"Label", "LowerLimit" , "UpperLimit"}, {{"Goods solds", 70700000 , 70799999}, {"Other sales", 70800000 , 70899999}, {"Services", 70500000, 70699999}}),
    Accounts = #table({"AccountNumber", "AccountName"}, {{70701000, "Local sales"}, {70800070, "Ancilary Sales"}, {70600000, "Services in Europe"}, {70610000, "International services"}, {70590000, "Local services"}, {70710000, "International Sales"}}),
    Lookup = Table.AddColumn(Accounts, "LookupCol", each Table.SelectRows(Criteria, (lookupcat) => [AccountNumber]>= lookupcat[LowerLimit] and [AccountNumber]<= lookupcat[UpperLimit])),
    Expand = Table.ExpandTableColumn(Lookup, "LookupCol",{"Label","LowerLimit", "UpperLimit"}, {"Label", "LowerLimit", "UpperLimit"})
in
    Expand

In the Expand step i only expand for the Label and so i have my accounts number whit their category depending of lower an upper limit.

As it is in M i coul add other steps and operations.

[Greg_Deckler]

If it's M, it's @ImkeF