Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

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

  • PC2790's avatar
    PC2790
    Community 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-yingjl's avatar
    v-yingjl
    Community 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.