The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now
Hi everyone,
I would need your help on the following case. I have 2 tables:
I would like to know, for each country in table 1, the number of text elements in table 2 that contain at least one occurrence of that country.
In the example below, the desired output will be:
Thank you in advance for your help, much appreciated!
Solved! Go to Solution.
Hi @amineregask
You need to remove that "each" after "Custom", before (X)=>
= Table.AddColumn(#"Changed Type1", "Custom", (x)=> List.Count(List.Select(Table2[Text], each Text.Contains(_,x[Country]))))
@watkinnc the way seems better, but there is a typo, you can try like this
Table.AddColumn(#"Changed Type1", "Custom", each List.Count(List.FindText(Table2[Text],[Country])))
Actually it looks like my typo is that it should read in Counts, not in counts...
let
Country2 = Table2[Text],
NewTable = Table1,
Counts = Table.AddColumn(NewTable, "Total", each List.Count(List.FindText(Country2, [Text]))
in Counts
--Nate
let
Country2 = Table2[Text],
NewTable = Table1,
Counts = Table.AddColumn(NewTable, "Total", each List.Count(List.FindText(Country2, [Text]))
in counts
--Nate
Hi @amineregask
If your Table2 and Text column are different names, please change it accordingly
(x)=> List.Count(List.Select(Table2[Text], each Text.Contains(_,x[Country])))
Hi @Vera_33 thanks a lot for your quick reply!
When I create the custom column, I get "function" values in it. Did I miss any step?
Thank you!
Hi @amineregask
You need to remove that "each" after "Custom", before (X)=>
= Table.AddColumn(#"Changed Type1", "Custom", (x)=> List.Count(List.Select(Table2[Text], each Text.Contains(_,x[Country]))))
@watkinnc the way seems better, but there is a typo, you can try like this
Table.AddColumn(#"Changed Type1", "Custom", each List.Count(List.FindText(Table2[Text],[Country])))