Forum Discussion
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 ...
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
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
Community ChampionCheck 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