Forum Discussion
Json document data in power query
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
2 Replies
- PC2790Community Champion
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
- v-yingjlCommunity Support
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.