Hi all,
An issue I have been struggling with for some weeks now (more like driving me insane tbh) is adding a column to my main data table showing what group an item belongs to. The group column should be based on the item name in the main table and a lookup table stored elsewhere.
It is quite important that it is done in M so it is done in the data modelling.
So basically I want the below output:
based on this lookup table:
I was thinking the solution would be something like this going into the column ***Desired solution***:
if Text.Contains([Item], [Lookup_value]) = True then [Lookup_return] else null
However, I believe that I hit a snag because I am evaluating a text against a list, but I am not sure.
Any and all help is appreciated
Best regards,
Christian
Solved! Go to Solution.
Hi @Schnohren
I think you can achieve your goal by merge and transform your table.
I build two sample tables like yours. I add a custom column in both tables for merge.
Merge = 1
Table1:
Lookup Table:
Choose Table1 to merge(lefter outer) two tables by merge columns.
Add a conditional column in New Table1.
Then we need to duplicate the New Table and group by Item to count null for each item.
We see item = pears 1 kg count = 10, others = 9, so we can distinguish that pears 1 kg = null others has result.
Merge New Table1 with this count table by Item, expand count column.
Remove "Merge","Lookup.Lookup_value","Lookup.Lookup_return" columns and remove duplicate column by selecting all remaining columns.
sort count column by ascending and sort Desired colution column by ascending.
Remove top 5 rows , count column and we can get the final result.
You can download the pbix file from this link: File.pbix
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @amitchandak ,
Thanks for your response. I don't think it quite qualifies, because in the example you referred we have a one-to-many relation where we have a key (the CustomerName).
Here I want to check the Item column to see if it conatins any of the strings you can find in the Lookup_value column from the other table. Ideally, i would like to return the corresponding Lookup_return value.
\Christian
Hi @Schnohren
I think you can achieve your goal by merge and transform your table.
I build two sample tables like yours. I add a custom column in both tables for merge.
Merge = 1
Table1:
Lookup Table:
Choose Table1 to merge(lefter outer) two tables by merge columns.
Add a conditional column in New Table1.
Then we need to duplicate the New Table and group by Item to count null for each item.
We see item = pears 1 kg count = 10, others = 9, so we can distinguish that pears 1 kg = null others has result.
Merge New Table1 with this count table by Item, expand count column.
Remove "Merge","Lookup.Lookup_value","Lookup.Lookup_return" columns and remove duplicate column by selecting all remaining columns.
sort count column by ascending and sort Desired colution column by ascending.
Remove top 5 rows , count column and we can get the final result.
You can download the pbix file from this link: File.pbix
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
The lookup.Lookup_value never works. This is useless solution
@Schnohren , refer if this solution , if that can help
User | Count |
---|---|
208 | |
83 | |
82 | |
79 | |
47 |
User | Count |
---|---|
165 | |
86 | |
83 | |
80 | |
74 |