Forum Discussion

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 ...

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

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

Forum Discussion

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

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