Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
Hi
I'm new to this forum, sorry, in case I don't meet the right wording. I tried to find similar threads here but without success.
I have a big table of data I import quite frequently. In one field is a text, quite random and up to 200 characters. Within this text could be keywords of different length and different positions I want to use to categorize data. For this I have a kind of Lookup table which provides me the category for those keywords (theoretically, this could lead to more than one category). I tried vlookup in excel with joker and found fuzzy vlookup as well, but the structure is differnet. I will try to sketch it here after.
Source (ca 5000 lines)
12/21/2023;this is a long text with a KEYWORD;additional data;4
12/19/2023;this is a text containg a TEXTSTRING which is good to define category;5
...
lookup table (ca 500 lines)
keyword; category
KEYWORD;long
TEXTSTRING;short
5232355;small
...
Desired result of an import / manipulation:
12/21/2023;this is a long text with a KEYWORD;additional data;4;long
12/19/2023;this text with TEXTSTRING which is good to define category;5;short
..
Hope the idea is clear.
If just the first category is shown, would be fine with me.
Any idea or hint?
thank you in advance
Lars
Solved! Go to Solution.
For each row of the Source table, you can filter the lookup table to only include rows where the text from the Source table contains the keyword in the lookup table. Then take the category column from the lookup table. This returns a result of all the category matches and you can take the first one.
let
Source = Table.FromRows(
{
{#date(2023, 12, 21), "this is a long text with a KEYWORD", "additional data", 4},
{#date(2023, 12, 19), "this is a text containg a TEXTSTRING which is good to define category", null, 5}},
type table [Date = date, Text = text, Data = text, Number = number]
),
lookup = Table.FromRows(
{{"KEYWORD", "long"}, {"TEXTSTRING", "short"}, {"5232355", "small"}},
type table [keyword = text, category = text]
),
Result = Table.AddColumn(
Source,
"lookup",
each List.First(
Table.SelectRows(
lookup,
(row) => Text.Contains([Text], row[keyword])
)[category]
),
type text
)
in
Result
That works great. Thank you so much!
For each row of the Source table, you can filter the lookup table to only include rows where the text from the Source table contains the keyword in the lookup table. Then take the category column from the lookup table. This returns a result of all the category matches and you can take the first one.
let
Source = Table.FromRows(
{
{#date(2023, 12, 21), "this is a long text with a KEYWORD", "additional data", 4},
{#date(2023, 12, 19), "this is a text containg a TEXTSTRING which is good to define category", null, 5}},
type table [Date = date, Text = text, Data = text, Number = number]
),
lookup = Table.FromRows(
{{"KEYWORD", "long"}, {"TEXTSTRING", "short"}, {"5232355", "small"}},
type table [keyword = text, category = text]
),
Result = Table.AddColumn(
Source,
"lookup",
each List.First(
Table.SelectRows(
lookup,
(row) => Text.Contains([Text], row[keyword])
)[category]
),
type text
)
in
Result
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
63 | |
56 | |
27 | |
17 | |
13 |