I own two tables. The sales ratio is the primary table. It has the name of the products. These names are lengthy, though, because the technical description is appended right after the name. For example: an iron bar comes as a 2' H type iron bar. The number of sold things is enormous.
The names of certain sold items are the only things on the second list. Consider an iron bar.
I therefore created a piece of code that adds a new column to the primary table and performs a search to see if any of the words from table 2 are present in that record. If it is discovered, table 2 item is returned. Thus, keeping the example, it find iron rod and return it to me.
My problem is when in the main list there is no item that is registered in the second table: it returns null. Then, in the next phase, Power Query throws an error claiming that it was unable to convert null to text.
Can you please help me to resolve this situation?
let
Fonte = Lista_gerada_pelo_programa,
#"Colunas Removidas" = Table.RemoveColumns(Fonte,{"Tomador - CNPJ", "Tomador - Razão Social", "CTe - Nº"}),
#"Personalização Adicionada" = Table.AddColumn(#"Colunas Removidas", "NOVA", each Table.SelectRows(Lista_Variacoes, (x)=> Text.Contains ([Produto], x[Variacoes], Comparer.OrdinalIgnoreCase) )),
#"NOVA_Expandido" = Table.ExpandTableColumn(#"Personalização Adicionada", "NOVA", {"Produto", "Variacoes"}, {"NOVA.Produto", "NOVA.Variacoes"})
in
#"NOVA_Expandido"
