How to combine raw table with with condition table in power query

[Yiqian]

Hello all Power BI experts,

Here I have a problem and I hope I can solve it with Power Query or power BI DAX.

I have two tables. One is raw data, that is like below,

Category	Value
a	70
b	50
c	40
d	30
e	60
f	10
g	80

And I also have one table with conditions, that is, for example,

Customer Name	Condition 1	Condition 2	Condition 3
Whisky	a + b > 100	a + b + c + d >200	b + c + d + e >250

In fact, I have several customers and all of them may have 3 or more conditions to see if these values have met standards.

If a + b > 100, that is, they meet the condition 1, both a and b will multiple, for example, 0.05 and I will get a new value.

How can I do this with Power Query ?

Thanks!

Best Regards,

Yiqian

[PwerQueryKees]

To keep it generic, I would do something like:

• Create a table with one record for each customer
• Put the M code of a function taking one record of your original table as parameter, returning true or false, in one of the colums. Call this column Validation.
• Add a custome colum named "Validate", with Expression.Evaluate([Validation], #shared)
• Mergejoin the new table with your original table on the customer id
• Expand Validate column 
• Add a new column with the expression [Validate](_)
• Pivot the customer column with that Validate column as value

Doing this without acces to my laptop... So there will be errors... But hopefully this shows you a direction that is easily extendible for any number of customers.....

[dufoq3]

Hi @Yiqian, it is possible, but you should provide expected result based on sample data.

You can find some inspiration here:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Jca5DQAgEAOwXVJTgHh3OVHw778BkeLKZhhwqB7dGSab1cUmdbNRPWxRLxvUxza2fw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Category = _t, Value = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"Value", type number}}),
    Buffered = Table.Buffer(ChangedType),
    CustomersTable = #table(type table[Customer=text, Condition=text, Multiply Parameter=number], {{"Whisky", "a + b > 100", 0.05}}),
    Ad_Condition1 = Table.AddColumn(CustomersTable, "Condition 1", each 
        [ a = Text.SplitAny([Condition], "+-><=*/"), 
          b = List.Select(a, (x)=> not ((try Number.From(x) otherwise x) is number)),
          c = List.Transform(b, (x)=> { x, Table.SelectRows(Buffered, (y)=> y[Category] = Text.Trim(x))[Value]{0}? }),
          d = List.Accumulate(c, [Condition], (x,y)=> Text.Replace(x, Text.Trim(y{0}), Text.From(y{1})) ),
          e = List.Transform(c, (x)=> x{1}),
          g = if Expression.Evaluate(d) then List.Transform(e, (x)=> x * [Multiply Parameter]) else e
        ][g], type number)
in
    Ad_Condition1

[Yiqian]

@dufoq3 

Hi Friend, many thanks for your help! I think it's a quite useful way but I haven't wrote M Query Language directly in Power Query yet s it's somewhat difficult for me to transfer it to my work.

Here I want to post two tables more directly.

Table A is conditions related to sales volume.

For example,

Customer Name	Condition 1	Condition 2	Condition 3
Whisky	Apple + Banana ≥ 200	Apple + Orange ≥ 400	Apple + Banana + Orange ≥ 700
Gin	Orange ≥ 250	Apple + Orange ≥ 650	null
Sherry	Banana ≥ 100	null	null

Table B is related to customers and yearly sales volume.

For example as below

Customer Name	Brand	Sales volume
Whisky	Apple	100
Whisky	Banana	200
Whisky	Orange	300
Gin	Apple	250
Gin	Orange	350
Sherry	Banana	100
Sherry	Pinapple	200

Up to now, I want to realize my first step, that is, to clean my data and follow up whether the sales volume has hit the condition 1/2/3 with a dashboard showing progress and percentage.

Your further help is highly appreciated.

Regards with many thanks,

Yiqian

[dufoq3 · Accepted solution]

Check this:

Output

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCs/ILM6uVNJRciwoyEkF0oYGBkqxOkgSTol5QAhkGKHL+Bcl5qWD9BhDZdwz85BMMjJFFkUohgoHZ6QWFaFYALMaLhOQmZcIMwwkFwsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Customer Name" = _t, Brand = _t, #"Sales volume" = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"Sales volume", type number}}),
    Buffered = Table.Buffer(ChangedType),
    ConditionTable = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCs/ILM6uVNJRciwoyElV0FZwSswDQoVHnUsVjAwMkCT8ixLz0lPBEiYoElAdKCrMgSpidaKV3DPzgCqRJIxMcZhpBpbIK83JAWsMzkgtKgK5C8k9hgZwJTCVsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Customer Name" = _t, #"Condition 1" = _t, #"Condition 2" = _t, #"Condition 3" = _t]),
    // You can probably delete this step.
    ReplaceTextNulls = Table.TransformColumns(ConditionTable, {}, each if _ = "null" then null else _),
    ReplacedValue = List.Accumulate(
        { {"≥", ">="}, {"≤", "<="} },
        ReplaceTextNulls, 
        (s,c)=> Table.ReplaceValue(s, c{0}, c{1}, Replacer.ReplaceText, Table.ColumnNames(s))
    ),
    Helper = [ conditionColumns = List.Select(Table.ColumnNames(ReplacedValue), (x)=> Text.StartsWith(x, "Condition", Comparer.OrdinalIgnoreCase)),
    delimiters = Text.ToList("+-/*><= ") ],
    StepBack = ReplacedValue,
    Ad_CheckColumns = List.Accumulate(
        Helper[conditionColumns],
        StepBack,
        (state, current)=> Table.AddColumn(state, current & " Check", each
                  [ a = Splitter.SplitTextByCharacterTransition((x)=> List.Contains(Helper[delimiters], x), (y)=> List.Contains(Helper[delimiters], y))(Record.Field(_, current)),
                    b = List.Transform(a, (x)=> [value = Table.SelectRows(Buffered, (y)=> y[Brand] = Text.Trim(x) and y[Customer Name] = [Customer Name]){0}?[Sales volume]?, result = if value = null then x else Text.From(value)][result]),
                    c = try Expression.Evaluate(Text.Combine(b)) otherwise null
                  ][c], type logical) ),
    UnpivotedOtherColumns = Table.UnpivotOtherColumns(Ad_CheckColumns, {"Customer Name"}, "Attribute", "Value"),
    FilteredRows = Table.SelectRows(UnpivotedOtherColumns, each Text.EndsWith([Attribute], "Check"))
in
    FilteredRows