Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- let
- ApiCallsTable = #table({}, {}),
- Rec = (Acc as table, OpenCursor as text, CursorId as text, depth as number) =>
- let
- Source = #"UserAccountsSearch-OpenCursor-SqlQuery"("select UID, isLite, accountType, profile, created, reqSource, loginProvider, data from accounts limit 300", OpenCursor, CursorId),
- ParsedResRecord = Json.Document(Source,65001),
- NextCursorId = Record.FieldOrDefault(ParsedResRecord, "nextCursorId", null),
- results = ParsedResRecord[results],
- CleanResult = List.Transform(results, (re) =>
- if Record.HasFields(re, {"loginProvider"})
- then re
- else Record.AddField(re, "loginProvider", null)
- ),
- ParsedResTable = Table.FromList(CleanResult, Splitter.SplitByNothing(), null, null, ExtraValues.Ignore),
- ExpandedTable = Table.ExpandRecordColumn(ParsedResTable, "Column1", {"UID", "created", "accountType", "profile","data", "loginProvider", "isLite"}, {"UID", "Created","accountType", "profile", "data", "loginProvider", "isLite"}),
- RecResult = if(NextCursorId = null) then
- Acc // Table.Combine({Acc, ExpandedTable})
- else
- @Rec(Table.Combine({Acc, ExpandedTable}), "", NextCursorId, depth+1)
- in
- RecResult,
- FinalRes = Rec(ApiCallsTable, "true", "", 0),
- #"Expanded profile" = Table.ExpandRecordColumn(FinalRes, "profile", {"lastName", "locale", "email", "firstName", "age", "gender", "country", "industry"}, {"lastName", "locale", "email", "firstName", "age", "gender", "country", "industry"}),
- #"Renamed Columns" = Table.RenameColumns(#"Expanded profile",{{"age", "Age"}, {"country", "Country"}, {"email", "Email"}, {"firstName", "FirstName"}, {"gender", "Gender"}, {"lastName", "LastName"}, {"locale", "Locale"}, {"loginProvider", "LoginProvider"}}),
- #"Duplicated Column1" = Table.DuplicateColumn(#"Renamed Columns", "Created", "Created - Copy"),
- #"Changed Type1" = Table.TransformColumnTypes(#"Duplicated Column1",{{"Created - Copy", type datetime}}),
- #"Renamed Columns1" = Table.RenameColumns(#"Changed Type1",{{"Created - Copy", "CreatedAsDateTime"}}),
- #"Expanded data" = Table.ExpandRecordColumn(#"Renamed Columns1", "data", {"optin", "initialAppSourceCode"}, {"data.optin", "Brand"}),
- #"Expanded data.optin" = Table.ExpandRecordColumn(#"Expanded data", "data.optin", {"optinSourceApplication"}, {"Campaign"})
- in
- #"Expanded data.optin"
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement