Hi
Iam Using a Api to import the total data from sharepoint subsites but when iam importing all the data from the subsites some values are duplicating and missing can you find a solution for thsi i used this code to import the api in batches 500 for each batch this is the code i used (Api is a dummy api).Please tell me if you have any idea.
let
//Team Users
Source = Json.Document(Web.Contents("https://sharepoint.com/sites/Perform/_api/search/query?querytext=%27ContentType:%20%22Team%20Users%2...",
[
Headers=[#"Accept"="application/json"]
])),
PrimaryQueryResult = Source[PrimaryQueryResult],
RelevantResults = PrimaryQueryResult[RelevantResults][TotalRows],
Offset = List.Generate(()=>0, each _ <= Number.RoundTowardZero( RelevantResults / 100)*100, each _+500),
//Convert the list to a table
OffsetToTable = Table.FromList(Offset, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
Terms = Table.RenameColumns(OffsetToTable,{{"Column1", "Term"}}),
#"Removed Top Rows" = Table.Skip(Terms,0),
Termset = Table.TransformColumnTypes(#"Removed Top Rows",{{"Term", type text}}),
SearchSuccessful = (Term) =>
let
Source = Json.Document(Web.Contents("https://sharepoint.com/sites/Perform/_api/search/query?querytext=%27ContentType:%20%22Team%20Users%2...",
[
Query=[#"startrow"=Term],
Headers=[#"Accept"="application/json"]
])),
Rows= Source[PrimaryQueryResult][RelevantResults][Table][Rows],
AllRows = List.Transform(Rows, each _[Cells]),
RowsToTables = List.Transform(AllRows, each List.Transform(_, each Record.ToTable(_))),
SkelToList = List.Transform(RowsToTables, each Table.FromList(_, Splitter.SplitByNothing(), null, null, ExtraValues.Error)),
CleanRows = List.Transform(SkelToList, each List.Transform(_[Column1], each Table.PromoteHeaders(Table.RemoveLastN( Table.RemoveColumns( _,{"Name"}), 1) ) ) ),
TransposeTable = Table.FromRows(List.Transform(CleanRows, each List.Transform(_, each Record.FieldValues(_{0}){0} ))),
ColumnRenames = List.Transform(CleanRows{0}, each { "Column" & Text.From( List.PositionOf(CleanRows{0}, _) + 1), Table.ColumnNames(_){0}}),
RenamedTable = Table.RenameColumns(TransposeTable, ColumnRenames)
in
RenamedTable ,
Output =
Table.AddColumn(Termset,"c",each SearchSuccessful([Term])),
#"Expanded c" = Table.ExpandTableColumn(Output, "c", {"UsersOWSUSER", "TeamID", "ListItemID", "OriginalPath"}, {"UsersOWSUSER", "TeamID", "ListItemID", "OriginalPath"}),
