Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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.
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.
Hi Marcelbeug,
It's work fine, thanks a lot !
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |