Json document data in power query

[Anonymous]

Hi all, 

can anyone tell my which part of this Json code is worng? I am seeing an error in power query saying " 

but I don't knwo how to fix it, here is the code 

let
Source = Json.Document(
Web.Contents(
"https://xxxxx.sharepoint.com/sites/pwa/_api/search/query?
querytext='ParentLink:Risks AND contentclass:STS_ListItem'&
rowlimit=500&
rowsperpage=500&
selectproperties=
'Title,
Author,
Path,
Description,
Write,
LastModifiedTime,
ParentLink,
SPWebUrl,
UniqueId'&
TrimDuplicates=false",
[Headers=[Accept="application/json"]]
)
),
PrimaryQueryResult = Source[PrimaryQueryResult],
RelevantResults = PrimaryQueryResult[RelevantResults],
Table = RelevantResults[Table],
#"Converted to Table" = Record.ToTable(Table),
#"Expanded Value" = Table.ExpandListColumn(#"Converted to Table", "Value"),
#"Expanded Value1" = Table.ExpandRecordColumn(#"Expanded Value", "Value", {"Cells"}, {"Value.Cells"}),
#"Removed Other Columns" = Table.SelectColumns(#"Expanded Value1",{"Value.Cells"}),
#"Added Index" = Table.AddIndexColumn(#"Removed Other Columns", "Index", 1, 1),
#"Expanded Value.Cells" = Table.ExpandListColumn(#"Added Index", "Value.Cells"),
#"Expanded Value.Cells1" = Table.ExpandRecordColumn(#"Expanded Value.Cells", "Value.Cells", {"Key", "Value", "ValueType"}, {"Value.Cells.Key", "Value.Cells.Value", "Value.Cells.ValueType"}),
#"Removed Columns" = Table.RemoveColumns(#"Expanded Value.Cells1",{"Value.Cells.ValueType"}),
#"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Value.Cells.Key]), "Value.Cells.Key", "Value.Cells.Value"),
#"Removed Columns1" = Table.RemoveColumns(#"Pivoted Column",{"Index"}),
#"Replaced Value" = Table.ReplaceValue(#"Removed Columns1","{","",Replacer.ReplaceText,{"UniqueId"}),
#"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","}","",Replacer.ReplaceText,{"UniqueId"}),
#"Lowercased Text" = Table.TransformColumns(#"Replaced Value1",{{"UniqueId", Text.Lower, type text}})
in
#"Lowercased Text"

Thanks for your help

[v-yingjl]

Hi @Anonymous ，

Please check whether the query could return the correct result

PrimaryQueryResult = Source[PrimaryQueryResult],

If not, it could be caused by the Source query, try to use Relative path and Query options in Web.Contents() to adjust your source query.

You can refer this blog which introduces it in details:

Using The RelativePath And Query Options With Web.Contents() In Power Query And Power BI M Code 

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[PC2790]

Hello,

Looks like something wrong with this part of the code:

RelevantResults = PrimaryQueryResult[RelevantResults],
Table = RelevantResults[Table],

Try doing hit and trial by removing this part.

Can suggest more if the sample data can be provided.

Thanks