Banding function with multiple variables

[Paul Walker]

Hello All,

I read Ken's post about Day Banding with great interest and want to do something similar but instead of passing just one variable to the function I want to pass two, I have put below my attempt to do this but it refuses to work.

let
    PWF= (LT,LU) =>


let values = {


    {(x)=>x="UCL",(y)=>y=1,"UCL1"},


    {LT,LU,"Undefined"}


    },


Result = List.First(List.Select(List.Select(values, each _ {0}=(LT)), each _ {1}=(LU))){2}


in Result


in PWF

Any pointers in the right direction here would be appreciated.

Thx

Paul

 

[Ken Puls]

Can you explain the logic of what you are trying to do, exactly? Run through a couple of sample scenarios?

 

[Paul Walker]

Hello Ken, I have a table and in one of the columns (Column B) is a product code and the plan was to create a new column (Column C) and put the more "friendly" name in there. The issue I ran into is that some of the Col B rows are blank so I have to look in another column (Column A) that contains a text description and look for keywords.


I can solve this issue using the IF formula in Excel but it is complex and difficult to read and modify, the IF THEN ELSE in Power Query is easier to read but is not flexible enough so I decided to create a function and do it myself in M.


I wanted to avoid using IF THEN ELSE in M because I felt it would again be difficult to read and modify, ideally I would use a CASE statement but M does not have that (an oversight in my humble opinion) so I did some Goggling which is where I found your Day Banding solution.


Your solution only has one parameter passed to it where I was planning to use two. The other issue that I had was your example uses equations in the List ( e.g. {(x)=>x<30...), other examples used absolute values (e.g. {"ABC", ...), I ideally wanted to combine both but my lack of knowledge of M was holding me back.


Since I made this post I have persevered with this and have actually got it to work, please see below for the function.


let
Source = (LU, LT) =>
let values=
{
{1, (x)=>Text.Contains(x,"a"), "1a"},
{2, (x)=>x="b", "2b"},
{3, (x)=>x="c", "3c"},
{LU,(x)=>x=LT, "Oth"}


},
Result = List.First(List.Select(List.Select(values, each _{0}=LU), each _{1}(LT))){2}
in
Result
in
Source


I would still appreciate your comments and although I have a working solution what I do not understand is part of the List.Select parameters, for absolute values you need "each _{0}=LU" but for equations you need "each _{1}(LT)" I would be grateful if you could explain that for me.


Also I have a "hardwired" List in the function, ideally my ultimate goal is to have a Table in an Excel Spreadsheet that has three columns


Param1 Param2 Output
1 A 1A
2 B 2B


and I would call the function and pass the table name so it can reference it. This will make it very easy for me to modify the parameters and have multiple tables. My guess at what the code would look like is ...


let
Source = (LU, LT, MyTable) =>
let values=
{
{each [@Param1], (x)=>Text.Contains(x,each [@Param2]), each [@Output]}


{LU,(x)=>x=LT, "Oth"}

},
Result = List.First(List.Select(List.Select(values, each _{0}=LU), each _{1}(LT))){2}
in
Result
in
Source


Any pointers in the right direction for this last bit will save me blowing a lot of fuses trying to work it out.


Many thanks


Paul


PS Got your book on order in order to boost my knowledge