Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi, Is there a way to import multiple conditions into Power Query "add new conditional column". I am adding new column based on set of multiple conditions "all of them are the "contains" condition" , however, my list of conditions is too long (>50) and keeps increasing, so I don't want to add conditions manually, appreciate if you areaware about an auto solution to link the conditions another query "lookup table".
Example: as you see below, I have monthly bank statements that contains almost the same transaction descriptions every month. I want power query to add new column "expense account" based on the description wording (e.g. if the transaction description contains "FACEBK", then the expense account should give "Advertising expense") something similar to bank rules in most accounting software (QBO/XERO).
as you see I have to add 50 clauses manually, I am looking for an option to import these 50 clauses (conditions) one time.
Thanks
Solved! Go to Solution.
Hi, @adaboul84
I encapsulated a function that replaces the value, which can be invoked on the column. This works for all columns.
Create a blank query, open Advanced Editor and replace the text there with the code below. In your original query, you can then go to the Add Column tab, invoke custom function and choose this function and choose your "Old" column as the input.
(inputtext as text) =>
let
LookupValue = Table.SelectRows(LookupTable,(y)=>Text.Contains(inputtext,y[Contain]))[New],
Result=if List.Count(LookupValue)=0 then inputtext else LookupValue{0}
in
Result
Result:
Please refer to the attachment below for details.
Hope this helps.
Best Regards,
Community Support Team _ Zeon Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, @adaboul84
I encapsulated a function that replaces the value, which can be invoked on the column. This works for all columns.
Create a blank query, open Advanced Editor and replace the text there with the code below. In your original query, you can then go to the Add Column tab, invoke custom function and choose this function and choose your "Old" column as the input.
(inputtext as text) =>
let
LookupValue = Table.SelectRows(LookupTable,(y)=>Text.Contains(inputtext,y[Contain]))[New],
Result=if List.Count(LookupValue)=0 then inputtext else LookupValue{0}
in
Result
Result:
Please refer to the attachment below for details.
Hope this helps.
Best Regards,
Community Support Team _ Zeon Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Alexis, thank for your reply, please see this screen shot of the lookup table. the description column in the bank statement and the lookup table "condition column" are not identical, even when I tried fuzzy match the result was not so good.
that's why I abandoned the merge queries option and focused on adding conditional column, because conditional columns with the operator selected to be "contains" gives accurate "expense accounts", but my pain it is time consuming to manually add clauses"conditions
See if my answer to this post helps:
Translating this to your column names:
(row) =>
List.Max(
Table.SelectRows(
LookupTable,
each Text.Contains(row[Description], [Condition])
)[Expense Account]
)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.