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, CategoryValuea70b50c40d30e60f10g80 And I also have one table with conditions, that is, for example, Customer NameCondition 1Condition 2Condition 3Whiskya + b > 100a + b + c + d >200b + 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

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

Yiqian

Regular Visitor

2 years ago

Solved

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

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

Need Help

dufoq3

2 years ago

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

4 Replies

dufoq3

Community Champion

2 years ago

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

Regular Visitor

2 years ago

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

Community Champion

2 years ago

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

PwerQueryKees
Super User
2 years ago
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.....

Forum Discussion

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

4 Replies

Related content

Tables combine

Conditional table combine

Combined conditional filtering

Combine / Append tables based on condition

How to get the conditional formatting for missing combinations