let
Source = let
a = (a) =>
List.Transform(
Excel.CurrentWorkbook(){[Name = a]}[Content][Столбец1],
Text.From
)
in
Table.FromColumns({a("Таблица1"), a("Таблица2")}),
Index = Table.AddIndexColumn(Source, "Индекс", 0, 1),
Filter = Table.SelectRows(Index, each [Column2] <> null and [Column2] <> ""),
Custom1 = Table.AddColumn(
Filter,
"a",
each let
json = Function.InvokeAfter(
()=> Json.Document(Web.Contents(
"https://www.instagram.com/graphql/query/?query_hash=472f257a40c653c64c666ce877d59d2b&variables={""id"":"""&[Column1]&""",""first"":50,""after"":"""&[Column2]&"""}"
)), #duration(0,0,0,5)),
tbl = Table.Combine(
List.Transform(json[data][user][edge_owner_to_timeline_media][edges], each Table.FromRecords({[node]?}))
),
transform = Table.ReplaceValue(tbl, null, null,
(a, b, c) => let
a = if a is record
then Record.ToList(a){0}
else a,
b = if a is list
then if a{0}[node]? <> null
then a{0}[node][text]
else Table.FromRecords(a)
else a
in
b,
{"edge_media_to_caption", "edge_media_to_comment", "edge_media_preview_like", "owner", "thumbnail_resources"}
),
expand = Table.ExpandRecordColumn(transform, "dimensions", {"height", "width"}, {"dimensions.height", "dimensions.width"}),
columns = List.Buffer(Table.ColumnNames(expand)),
indexed = Table.ToRecords(Table.AddIndexColumn(expand,"i")),
records = List.Transform(indexed,(r)=>Record.RenameFields(Record.RemoveFields(r,"i"),List.Zip({columns,List.Transform(columns, each Text.From(r[i])&"|"&_)})))
in
if Table.RowCount(tbl) > 0 then Table.FromRecords({Record.Combine(records)}) else #table({"dummy"},{{null}})
),
Merge = Table.NestedJoin(Index,{"Индекс"},Custom1,{"Индекс"},"Custom1",JoinKind.LeftOuter),
Expand = Table.ExpandTableColumn(Merge, "Custom1", {"a"}, {"a"}),
#"Changed Type" = Table.TransformColumns(Expand,{{"a", each if _=null then #table({"dummy"},{{null}}) else _}}),
#"Sorted Rows" = Table.Sort(#"Changed Type",{{"Индекс", Order.Ascending}}),
#"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",{"Индекс"}),
Custom2 = Table.Combine(
Table.ReplaceValue( #"Removed Columns", each [Column1], each [Column2],
(a, b, c) => Table.AddColumn(Table.AddColumn(a, "ID владельца", each b), "after", each c),
{"a"}
)[a]
),
Demote = Table.DemoteHeaders(Table.RemoveColumns(Custom2,{"dummy"},2)),
Transp = Table.Transpose(Demote),
Split = Table.SplitColumn(Transp, "Column1", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), 2),
Replace = Table.ReplaceValue(Split,"id","ID публикации",Replacer.ReplaceValue,{"Column1.2"}),
Result = Table.Transpose(Replace)
in
Result
|
