Advertisement
Guest User

Untitled

a guest
Apr 25th, 2019
85
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 2.50 KB | None | 0 0
  1. let
  2. ApiCallsTable = #table({}, {}),
  3. Rec = (Acc as table, OpenCursor as text, CursorId as text, depth as number) =>
  4. let
  5. Source = #"UserAccountsSearch-OpenCursor-SqlQuery"("select UID, isLite, accountType, profile, created, reqSource, loginProvider, data from accounts limit 300", OpenCursor, CursorId),
  6.  
  7. ParsedResRecord = Json.Document(Source,65001),
  8. NextCursorId = Record.FieldOrDefault(ParsedResRecord, "nextCursorId", null),
  9.  
  10. results = ParsedResRecord[results],
  11.  
  12. CleanResult = List.Transform(results, (re) =>
  13. if Record.HasFields(re, {"loginProvider"})
  14. then re
  15. else Record.AddField(re, "loginProvider", null)
  16. ),
  17.  
  18. ParsedResTable = Table.FromList(CleanResult, Splitter.SplitByNothing(), null, null, ExtraValues.Ignore),
  19.  
  20. ExpandedTable = Table.ExpandRecordColumn(ParsedResTable, "Column1", {"UID", "created", "accountType", "profile","data", "loginProvider", "isLite"}, {"UID", "Created","accountType", "profile", "data", "loginProvider", "isLite"}),
  21.  
  22. RecResult = if(NextCursorId = null) then
  23. Acc // Table.Combine({Acc, ExpandedTable})
  24. else
  25. @Rec(Table.Combine({Acc, ExpandedTable}), "", NextCursorId, depth+1)
  26.  
  27. in
  28. RecResult,
  29.  
  30. FinalRes = Rec(ApiCallsTable, "true", "", 0),
  31. #"Expanded profile" = Table.ExpandRecordColumn(FinalRes, "profile", {"lastName", "locale", "email", "firstName", "age", "gender", "country", "industry"}, {"lastName", "locale", "email", "firstName", "age", "gender", "country", "industry"}),
  32. #"Renamed Columns" = Table.RenameColumns(#"Expanded profile",{{"age", "Age"}, {"country", "Country"}, {"email", "Email"}, {"firstName", "FirstName"}, {"gender", "Gender"}, {"lastName", "LastName"}, {"locale", "Locale"}, {"loginProvider", "LoginProvider"}}),
  33. #"Duplicated Column1" = Table.DuplicateColumn(#"Renamed Columns", "Created", "Created - Copy"),
  34. #"Changed Type1" = Table.TransformColumnTypes(#"Duplicated Column1",{{"Created - Copy", type datetime}}),
  35. #"Renamed Columns1" = Table.RenameColumns(#"Changed Type1",{{"Created - Copy", "CreatedAsDateTime"}}),
  36. #"Expanded data" = Table.ExpandRecordColumn(#"Renamed Columns1", "data", {"optin", "initialAppSourceCode"}, {"data.optin", "Brand"}),
  37. #"Expanded data.optin" = Table.ExpandRecordColumn(#"Expanded data", "data.optin", {"optinSourceApplication"}, {"Campaign"})
  38. in
  39. #"Expanded data.optin"
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement