Issues with NULL in an added and custom column

0 votes

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"
Mar 24, 2023 in Others by narikkadan
• 63,420 points
220 views

1 answer to this question.

0 votes

Try this:

    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"
answered Mar 24, 2023 by Kithuzzz
• 38,010 points

Related Questions In Others

0 votes
1 answer

How do I get an Excel range using row and column numbers in VSTO / C#?

Use: int countRows = xlWorkSheetData.UsedRange.Rows.Count; int countColumns = xlWorkSheetData.UsedRange.Columns.Count; object[,] ...READ MORE

answered Nov 17, 2022 in Others by narikkadan
• 63,420 points
1,265 views
0 votes
1 answer
0 votes
1 answer

Creating sheets with names in column B and assigning data to each sheet for particular name

after the first occurrence of Set sht = ...READ MORE

answered Feb 13, 2023 in Others by narikkadan
• 63,420 points
478 views
0 votes
1 answer

Excel Power Query: Using List.MatchAny on a column value

try this. let TableA = ...READ MORE

answered Oct 22, 2018 in Power BI by Annie97
• 2,160 points
3,789 views
0 votes
1 answer

Power Query : http POST request with form data

Try using Uri.BuildQueryString and Json.Document. let ...READ MORE

answered Dec 12, 2018 in Power BI by Upasana
• 8,620 points
8,289 views
0 votes
1 answer

Power Query : Adding Columns and Multiple files

The following call, FirstRowAsHeader = Table.PromoteHeaders(TableWithoutHeader) shall replace the ...READ MORE

answered Feb 14, 2019 in Power BI by Upasana
• 8,620 points
1,421 views
0 votes
1 answer

Power Query : making HTTP POST request with form data

Try Using Uri.BuildQueryString and Json.Document let ...READ MORE

answered Feb 14, 2019 in Power BI by Upasana
• 8,620 points
5,630 views
0 votes
1 answer
0 votes
1 answer

What VBA code would I use to concatenate cell A2 & B2 in cell C2 and then have it Autofill down the column?

Solution Find the last row. Write a formula to ...READ MORE

answered Feb 14, 2023 in Others by Kithuzzz
• 38,010 points
628 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP