Structuring and cleaning a column by using reference tables

[Anonymous]

Hi,

I am building an app to analyse my bank transactions. These transactions have two columns that contain more or less information stored in an unstructured manner. My plan is to use reference tables (that I already have loaded into the model) to extract and structure data from these two columns. I have for example tables with places, country codes, and company names.

I have solved this problem with DAX formula's.

First I look into the reference table to find data using the following formula:

MAXX(FILTER(Country, SEARCH(" " & Country[Country code], Transaction[Description],,0)>0),Country[Country code])

Then I will calculate a new column which removes the country code, and then I will use this column to search for the next one.

My problem:

My approach convolutes the model with data I only use for ETL purpoces. Therefore I would like to solve this problem in Power Query.

Question:

How do I solve this problem in Power Query?

[v-kkf-msft]

Hi @Anonymous ,

Has your problem been solved? If it is solved, please mark a reply which is helpful to you.

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

[v-kkf-msft]

Hi @Anonymous ,

Are you trying to implement the logic of the above DAX in PQ? If so, please try the following custom column.

= 
let 
   CurrentDec = [Description], 
   SelectRows = Table.SelectRows(Country, each Text.Contains(CurrentDec, " " & [Country code], Comparer.OrdinalIgnoreCase))[Country code]
in 
   List.Max(SelectRows)

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lbendlin]

Please provide sanitized sample data that fully covers your issue. If you paste the data into a table in your post or use one of the file services it will be easier to assist you. I cannot use screenshots of your source data.

Please show the expected outcome based on the sample data you provided. Screenshots of the expected outcome are ok.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523