Managing Errors in M-Language Query (Advanced Editor)

[Dom33]

HI,

Input data are in a json file like that:

{
    "lastname":{
        "columns_found":2,
        "columns":[
            {
                "location":"['Table1'].['DESCRIPTION']",
                "nb_rows_scaned":"1000",
                "pc_personnal_data":"0.6",
                "data_found":"{'Thomas', 'Rouge', 'Martin', 'Dubos', 'Bureau', 'Masse'}"
            },
            {
                "location":"['Table3'].['PRENOM']",
                "nb_rows_scaned":"1000",
                "pc_personnal_data":"5.1",
                "data_found":"{'Perrier', 'Blandin', 'Lafond', 'Renault', 'Guillet', 'Claude', 'Legrand'}"
            }
        ]
    }
}

In advanced editor the requeste in M-Language is:

let
    Source = Json.Document(File.Contents("C:\carto\results-tst.json")),
    lastname = Source[lastname],
    columns = lastname[columns],
    #"Converted to Table" = Table.FromList(columns, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"location", "nb_rows_scaned", "pc_personnal_data", "data_found"}, {"location", "numbers_of_rows_analyzed", "percentage_of_personnal_data", "data_found"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Column1",{{"data_found", type text}})
in
    #"Changed Type"

It works I am happy but sometime in the json file I have that:

{
    "lastname":{
        "columns_found":0,
        "columns":[]
    }
}

And then I have an error message Expression.Error column Column1 not found.

This is a test file, in real I have several blocs of data to read (firstname, lastname, birthdate...) and the problem may occur anywhere in the input file.

The M-language seems to offer a lot of possibilities but I do not know it.
Is it possible to modify the query to put a default value in the  variable column when we have "columns_found": 0 in the file. So Table.Fromlist will work and column1 will be filled.

Thanks,

Dominique

[MarcelBeug · Accepted solution]

Replace the first null in #"Converted to Table" by 1:

    #"Converted to Table" = Table.FromList(columns, Splitter.SplitByNothing(), 1, null, ExtraValues.Error),

This will force the table to be created with 1 column.

[Dom33]

Hi Marcelbeug,

It's work fine, thanks a lot !