Search code examples
powerbipowerquerypowerbi-desktop

Error in output if column cell is empty or null in Power BI

While extracting a pattern from a text column 'Column1' into another column 'Release' in Power BI, I am getting an error if the column 'Column1' has an empty or null value. I have below code which works for non empty values but gives error if 'column1' cell is empty or null. I would like to skip the empty and null column values. How to fix this?

let
    Source = Excel.Workbook(File.Contents("C:\Users\aditya\Documents\Test.xlsx"), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Sheet1_Sheet,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}}),
    #"Added Release" = Table.AddColumn(#"Changed Type", "Release", each let a = List.Transform(Text.Split([Column1], ";"), each Text.Trim(_)),
    b = List.Select(a, each Text.StartsWith(_,"R") and Value.Is(Value.FromText(Text.At(_, 1)), type number))
    in b{0}?)
    in
    #"Added Release"

enter image description here

Solution

  • It is failing on the Text.Split function in the #"Added Release" step.

    Convert the null into "" so it won't fail on the Text.Split function:

    a = List.Transform(Text.Split([Column1]??"", ";"), each Text.Trim(_)),