Problem with importing json data from web

[Johannessen54]

I need some help regarding importing json data from web. I need the report to show OK when site is empty and when it contains data i need to show data in my Report.  But no data is showing when the site contains data.

I use this:

let
Source = Json.Document(Web.Contents("http://something her")),
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
Table1=Table.FromRecords({
[Status = "OK", ID = "", ProcessID = "", Time="", Activityid="", Messages=""]

}),
result = if List.IsEmpty(source) then Table1 else
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table",[Status = "Failed"], "Column1", {"id", "processDefinitionId", "incidentTimestamp", "activityId", "incidentMessage"}, {"Column1.id", "Column1.processDefinitionId", "Column1.incidentTimestamp", "Column1.activityId", "Column1.incidentMessage"}),
#"Filtered Rows" = Table.SelectRows(#"Expanded Column1", each true),
#"Renamed Columns" = Table.RenameColumns(#"Filtered Rows",{{"Column1.id", "ID"}, {"Column1.processDefinitionId", "ProcessID"}, {"Column1.incidentTimestamp", "Time"}, {"Column1.activityId", "Activityid"}, {"Column1.incidentMessage", "Messages"}})
in
result

Any suggestions whats wrong with my report?

[v-jiascu-msft · Accepted solution]

Hi @Johannessen54,

Does it work in your side? 

I'm afraid we can't use if-then-else like that. Maybe we can nest these functions. Please check out the demo in the attachment.

let
    Source = Json.Document(Web.Contents("http://api.plos.org/search?q=title:DNA")),
    response = Source[response],
    docs = response[docs],
    Table1=Table.FromRecords({[Status = "OK", ID = "", ProcessID = "", Time="", Activityid="", Messages=""]}),
    result = if List.IsEmpty(docs) then Table1 else  Table.ExpandRecordColumn(Table.FromList(docs, Splitter.SplitByNothing(), null, null, ExtraValues.Error), "Column1", {"id", "journal", "eissn", "publication_date", "article_type", "author_display", "abstract", "title_display", "score"}, {"Column1.id", "Column1.journal", "Column1.eissn", "Column1.publication_date", "Column1.article_type", "Column1.author_display", "Column1.abstract", "Column1.title_display", "Column1.score"})
in
    result

It woks.

Best Regards,
Dale