I have a database with a key column "Info" that consists of CampaignID + CountryCode + AdID + Version.
And I have a table called "Settings" with two rows: "CountryCode" and "CountryName".
(Please see example below)
I would like to add a column Country Name in the database. So I need a function that searches values in column SETTINGS[CountryCode] in the string DATABASE[Info] and returns SETTINGS[CountryName]
This is an example. Then I would need to replicate it for Campaign, Ad and Version.
I hope you can help me with this....
EDIT: I've uploaded a more realistic example. There's no structure in the Text String in "Info", so I can't split the string...
Why not just split the column using - as the delimiter, then reconstruct the country column and then relate the main fact table to your settings table(s)?
@jthomsonis right. You can achieve this in the PBI Query Editor if you know how it works or creating a calculated column in Power BI if your Info column has the same number of characters in all rows. In this last case, use this formula:
Finally, you can join the tables with this field or you can create another column in the DATABASE table with the CountryName information using the function RELATED.
Thanks for your reply.
The issue is that the Column "Info" is not structured at all (I only posted an example). The delimiters are not always there and names can be totally different.
For instance, the country Flanders can be BE-NL, BE_NL, BENL, BE(NL), etc.
I see your problem, I definitively would use the Query Editor to do all the transformations you need.
For instance, I'd do:
1. Replace by a space the special characters: - / _ ( ) and so on.
2. Split column by the delimiter (space).
3. Unpivot the columns.
4. Add a conditional column: if our column has 2 characters = true, else false.
5. Filter the new column by true.
6. Remove this column.
This is an example of you can achieve in the Query Editor. Good luck normalizing your countries.
Thanks for your reply, Jorge.
This would be a quick solution for a one-time thing.
But I have to update the database every week and we will always have new (and different) "Info".
And I want to solve this issue not only for country, but also Campaign ID or Campaign Name, Social Media (FB, IG, LI, etc), Ad Version, etc.
In sum, I need to "split" the "Info" column into around 5 new fields. Since there's no structure in the data, the best solution would be to use sort of dimension tables: I look for the value in the tables, if this value is the text string, return the corresponding name.
In Excel I would do it by using a array INDEX(MATCH(), but I have no idea how to do it in Power Query.
You need to clean and transform your data to normalize it. To solve it you need an ETL software that makes for you the transformations each time. Power BI gives you the option to do it with the query editor (Power Query). Initially, it seems difficult to learn but you'll see that this not true and you'll be able to do marvellous things in a short time.
So this means that there's no way of performing the search???
Search Value in DimensionTable[ColumnX] in Text String of FactTable[ColumnA] and return DimensionTable[ColumnY]
With DAX functions I don´t think the complete process could be achieve. In the Query Editor you could do:
1. Merge the tables as a Full Outer Join. This generate a cartesian product.
2. Add a column using the function Text.Contains.
3. Remove false rows from this column.
4. Remove the columns you don't want.
Here it is a solution. However, this is a workaround for a normalized data issue that I'd encourage you to solve for a better future.