Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Dom33
Regular Visitor

Managing Errors in M-Language Query (Advanced Editor)

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

 

 

 

1 ACCEPTED SOLUTION
MarcelBeug
Community Champion
Community Champion

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.

Specializing in Power Query Formula Language (M)

View solution in original post

2 REPLIES 2
MarcelBeug
Community Champion
Community Champion

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.

Specializing in Power Query Formula Language (M)

Hi Marcelbeug,

It's work fine, thanks a lot ! Smiley Happy

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.