Missing Field.Ignore

[JulianaMacedo]

Hi! I'm trying to use this Missing Field Ignore in a step I created, but I'm getting an error and I couldn't figure out why.

The data is basically a table with a column with numbers as strings like this one:

100100000000000000000000000000000000000

Which are split in columns for every each 8 digits and then this is what I'm trying to apply:

The whole thing is working, he only thing is that when the column has null values then it gives an error.

=Table.TransformColumns(#"Split Column by Position", List.Transform(#"List of Columns to Create", each {_, each Text.PositionOfAny(Text.Reverse(Text.Range(_,0,Text.Length(_))),{"1"},8) }), MissingField.Ignore)

 This is the error:

Expression.Error: We cannot convert the value 1 to type Function. Details: Value=1 Type=[Type]​

Any ideas why and how to solve it?

[jennratten]

Hello - please try this...

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQwACKCQClWJ1oprzQnB8wgoEkpNhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    Source1 = Table.ReplaceValue(Source,"null",null,Replacer.ReplaceValue,{"Column1"}),
    // replace this list with your list of column names
    #"List of Columns to Create" = {"Column1.1", "Column1.2", "Column1.3", "Column1.4", "Column1.5"},
    #"Split Column by Position" = Table.SplitColumn(Source1, "Column1", Splitter.SplitTextByRepeatedLengths(8), #"List of Columns to Create")
in
    #"Split Column by Position"

[JulianaMacedo]

Hi! Thanks for your contribution, I actually tried the other suggest solution first and it seems to be working fine, so I haven't tried yours, but it also seems like it would be a suitable solution.

[Anonymous · Accepted solution]

Hi @JulianaMacedo ,

You may use try and otherwise syntax:

Error handling - Power Query | Microsoft Docs

Best Regards,
Eyelyn Qin

[JulianaMacedo]

Thanks a lot! I didn't know there was a try clause in power bi, it did work!