let Source = Json.Document(Web.Contents("https://bountyboard.bankless.community/api/bounties")), #"Converted to Table" = Table.FromRecords({Source}), #"Expanded data" = Table.ExpandListColumn(#"Converted to Table", "data"), #"Expanded data1" = Table.ExpandRecordColumn(#"Expanded data", "data", {"_id", "season", "isParent", "childrenIds", "activityHistory", "activities", "title", "description", "criteria", "reward", "createdBy", "createdAt", "createdInChannel", "statusHistory", "status", "paidStatus", "dueAt", "gate", "discordMessageId", "requireApplication", "applicants", "assign", "assignedName", "__v", "evergreen", "parentId", "assignTo", "claimedAt", "gateTo", "claimLimit", "canonicalCard", "isIOU", "claimedBy", "submissionNotes", "submissionUrl", "submittedAt", "submittedBy", "claimantMessage", "creatorMessage", "paidAt", "paidBy", "resolutionNote", "reviewedAt", "reviewedBy", "editKey", "customerId", "customer_id", "paidStatusIdx", "statusIdx"}, {"data._id", "data.season", "data.isParent", "data.childrenIds", "data.activityHistory", "data.activities", "data.title", "data.description", "data.criteria", "data.reward", "data.createdBy", "data.createdAt", "data.createdInChannel", "data.statusHistory", "data.status", "data.paidStatus", "data.dueAt", "data.gate", "data.discordMessageId", "data.requireApplication", "data.applicants", "data.assign", "data.assignedName", "data.__v", "data.evergreen", "data.parentId", "data.assignTo", "data.claimedAt", "data.gateTo", "data.claimLimit", "data.canonicalCard", "data.isIOU", "data.claimedBy", "data.submissionNotes", "data.submissionUrl", "data.submittedAt", "data.submittedBy", "data.claimantMessage", "data.creatorMessage", "data.paidAt", "data.paidBy", "data.resolutionNote", "data.reviewedAt", "data.reviewedBy", "data.editKey", "data.customerId", "data.customer_id", "data.paidStatusIdx", "data.statusIdx"}), #"Expanded data.reward" = Table.ExpandRecordColumn(#"Expanded data1", "data.reward", {"currency", "amount", "scale", "amountWithoutScale"}, {"data.reward.currency", "data.reward.amount", "data.reward.scale", "data.reward.amountWithoutScale"}), #"Expanded data.createdBy" = Table.ExpandRecordColumn(#"Expanded data.reward", "data.createdBy", {"discordHandle", "discordId", "iconUrl"}, {"data.createdBy.discordHandle", "data.createdBy.discordId", "data.createdBy.iconUrl"}), #"Split Column" = Table.SplitColumn(#"Expanded data.createdBy", "data.gate", each _, {"data.gate.0"}), #"Expanded data.assignTo" = Table.ExpandRecordColumn(#"Split Column", "data.assignTo", {"discordId", "discordHandle", "iconUrl"}, {"data.assignTo.discordId", "data.assignTo.discordHandle", "data.assignTo.iconUrl"}), #"Expanded data.canonicalCard" = Table.ExpandRecordColumn(#"Expanded data.assignTo", "data.canonicalCard", {"messageId", "channelId"}, {"data.canonicalCard.messageId", "data.canonicalCard.channelId"}), #"Expanded data.claimedBy" = Table.ExpandRecordColumn(#"Expanded data.canonicalCard", "data.claimedBy", {"discordHandle", "discordId", "iconUrl"}, {"data.claimedBy.discordHandle", "data.claimedBy.discordId", "data.claimedBy.iconUrl"}), #"Expanded data.submittedBy" = Table.ExpandRecordColumn(#"Expanded data.claimedBy", "data.submittedBy", {"discordHandle", "discordId", "iconUrl"}, {"data.submittedBy.discordHandle", "data.submittedBy.discordId", "data.submittedBy.iconUrl"}), #"Expanded data.claimantMessage" = Table.ExpandRecordColumn(#"Expanded data.submittedBy", "data.claimantMessage", {"messageId", "channelId"}, {"data.claimantMessage.messageId", "data.claimantMessage.channelId"}), #"Expanded data.creatorMessage" = Table.ExpandRecordColumn(#"Expanded data.claimantMessage", "data.creatorMessage", {"messageId", "channelId"}, {"data.creatorMessage.messageId", "data.creatorMessage.channelId"}), #"Expanded data.paidBy" = Table.ExpandRecordColumn(#"Expanded data.creatorMessage", "data.paidBy", {"discordHandle", "discordId", "iconUrl"}, {"data.paidBy.discordHandle", "data.paidBy.discordId", "data.paidBy.iconUrl"}), #"Expanded data.reviewedBy" = Table.ExpandRecordColumn(#"Expanded data.paidBy", "data.reviewedBy", {"discordHandle", "discordId", "iconUrl"}, {"data.reviewedBy.discordHandle", "data.reviewedBy.discordId", "data.reviewedBy.iconUrl"}), #"Changed Type" = Table.TransformColumnTypes(#"Expanded data.reviewedBy",{{"success", type logical}, {"data._id", type text}, {"data.season", Int64.Type}, {"data.isParent", type any}, {"data.childrenIds", type any}, {"data.activityHistory", type any}, {"data.activities", type any}, {"data.title", type text}, {"data.description", type text}, {"data.criteria", type text}, {"data.reward.currency", type text}, {"data.reward.amount", Int64.Type}, {"data.reward.scale", Int64.Type}, {"data.reward.amountWithoutScale", type any}, {"data.createdBy.discordHandle", type text}, {"data.createdBy.discordId", Int64.Type}, {"data.createdBy.iconUrl", type text}, {"data.createdAt", type datetime}, {"data.createdInChannel", type any}, {"data.statusHistory", type any}, {"data.status", type text}, {"data.paidStatus", type any}, {"data.dueAt", type datetime}, {"data.gate.0", type any}, {"data.discordMessageId", Int64.Type}, {"data.requireApplication", type any}, {"data.applicants", type any}, {"data.assign", type any}, {"data.assignedName", type any}, {"data.__v", type any}, {"data.evergreen", type any}, {"data.parentId", type any}, {"data.assignTo.discordId", type any}, {"data.assignTo.discordHandle", type any}, {"data.assignTo.iconUrl", type any}, {"data.claimedAt", type datetime}, {"data.gateTo", type any}, {"data.claimLimit", type any}, {"data.canonicalCard.messageId", type any}, {"data.canonicalCard.channelId", type any}, {"data.isIOU", type any}, {"data.claimedBy.discordHandle", type text}, {"data.claimedBy.discordId", Int64.Type}, {"data.claimedBy.iconUrl", type text}, {"data.submissionNotes", Int64.Type}, {"data.submissionUrl", type text}, {"data.submittedAt", type datetime}, {"data.submittedBy.discordHandle", type text}, {"data.submittedBy.discordId", Int64.Type}, {"data.submittedBy.iconUrl", type text}, {"data.claimantMessage.messageId", type any}, {"data.claimantMessage.channelId", type any}, {"data.creatorMessage.messageId", type any}, {"data.creatorMessage.channelId", type any}, {"data.paidAt", type any}, {"data.paidBy.discordHandle", type any}, {"data.paidBy.discordId", type any}, {"data.paidBy.iconUrl", type any}, {"data.resolutionNote", type any}, {"data.reviewedAt", type datetime}, {"data.reviewedBy.discordHandle", type text}, {"data.reviewedBy.discordId", Int64.Type}, {"data.reviewedBy.iconUrl", type text}, {"data.editKey", type text}, {"data.customerId", Int64.Type}, {"data.customer_id", Int64.Type}, {"data.paidStatusIdx", Int64.Type}, {"data.statusIdx", Int64.Type}, {"results", Int64.Type}, {"previous", type text}, {"hasPrevious", type logical}, {"next", type text}, {"hasNext", type logical}}), #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"data._id", "id"}, {"data.season", "season"}, {"data.title", "title"}, {"data.description", "description"}, {"data.criteria", "criteria"}, {"data.reward.currency", "reward.currency"}, {"data.reward.amount", "reward.amount"}, {"data.reward.scale", "reward.scale"}, {"data.reward.amountWithoutScale", "reward.amountWithoutScale"}, {"data.createdBy.discordHandle", "createdBy.discordHandle"}, {"data.createdBy.discordId", "createdBy.discordId"}}), #"Renamed Columns1" = Table.RenameColumns(#"Renamed Columns",{{"data.createdAt", "createdAt"}, {"data.createdInChannel", "createdInChannel"}}), #"Expanded data.statusHistory" = Table.ExpandListColumn(#"Renamed Columns1", "data.statusHistory"), #"Expanded data.statusHistory1" = Table.ExpandRecordColumn(#"Expanded data.statusHistory", "data.statusHistory", {"status", "setAt", "modifiedAt"}, {"data.statusHistory.status", "data.statusHistory.setAt", "data.statusHistory.modifiedAt"}), #"Renamed Columns2" = Table.RenameColumns(#"Expanded data.statusHistory1",{{"data.statusHistory.status", "statusHistory.status"}, {"data.statusHistory.setAt", "statusHistory.setAt"}}), #"Filtered Rows" = Table.SelectRows(#"Renamed Columns2", each ([statusHistory.status] <> "Draft")), #"Renamed Columns3" = Table.RenameColumns(#"Filtered Rows",{{"data.statusHistory.modifiedAt", "statusHistory.modifiedAt"}}), #"Filtered Rows1" = Table.SelectRows(#"Renamed Columns3", each true), #"Renamed Columns4" = Table.RenameColumns(#"Filtered Rows1",{{"data.status", "status"}, {"data.paidStatus", "paidStatus"}}), #"Filtered Rows2" = Table.SelectRows(#"Renamed Columns4", each true), #"Renamed Columns5" = Table.RenameColumns(#"Filtered Rows2",{{"data.dueAt", "dueAt"}}), #"Renamed Columns6" = Table.RenameColumns(#"Renamed Columns5",{{"data.discordMessageId", "discordMessageId"}, {"data.requireApplication", "requireApplication"}, {"data.applicants", "applicants"}, {"data.assign", "assign"}, {"data.assignedName", "assignedName"}}), #"Renamed Columns8" = Table.RenameColumns(#"Renamed Columns6",{{"data.claimLimit", "claimLimit"}, {"data.canonicalCard.messageId", "canonicalCard.messageId"}, {"data.canonicalCard.channelId", "canonicalCard.channelId"}, {"data.isIOU", "isIOU"}, {"data.claimedBy.discordHandle", "claimedBy.discordHandle"}, {"data.claimedBy.discordId", "claimedBy.discordId"}, {"data.claimedBy.iconUrl", "claimedBy.iconUrl"}, {"data.submissionNotes", "submissionNotes"}, {"data.submissionUrl", "submissionUrl"}, {"data.submittedAt", "submittedAt"}, {"data.submittedBy.discordHandle", "submittedBy.discordHandle"}, {"data.submittedBy.discordId", "submittedBy.discordId"}, {"data.submittedBy.iconUrl", "submittedBy.iconUrl"}, {"data.claimantMessage.messageId", "claimantMessage.messageId"}, {"data.claimantMessage.channelId", "claimantMessage.channelId"}, {"data.creatorMessage.messageId", "creatorMessage.messageId"}, {"data.creatorMessage.channelId", "creatorMessage.channelId"}, {"data.paidAt", "paidAt"}, {"data.paidBy.discordHandle", "paidBy.discordHandle"}, {"data.paidBy.discordId", "paidBy.discordId"}, {"data.paidBy.iconUrl", "paidBy.iconUrl"}, {"data.resolutionNote", "resolutionNote"}, {"data.reviewedAt", "reviewedAt"}, {"data.reviewedBy.discordHandle", "reviewedBy.discordHandle"}, {"data.reviewedBy.discordId", "reviewedBy.discordId"}, {"data.reviewedBy.iconUrl", "reviewedBy.iconUrl"}, {"data.editKey", "editKey"}, {"data.customerId", "customerId"}, {"data.customer_id", "customer_id"}, {"data.paidStatusIdx", "paidStatusIdx"}, {"data.statusIdx", "statusIdx"}}), #"Sorted Rows" = Table.Sort(#"Renamed Columns8",{{"claimantMessage.messageId", Order.Ascending}}), #"Changed Type1" = Table.TransformColumnTypes(#"Sorted Rows",{{"statusHistory.setAt", type datetime}, {"statusHistory.modifiedAt", type datetime}}), #"Filtered Rows3" = Table.SelectRows(#"Changed Type1", each true), #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows3",{"success"}), #"Removed Duplicates" = Table.Distinct(#"Removed Columns", {"id"}), #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Removed Duplicates", {{"customerId", type text}, {"customer_id", type text}}, "en-US"),{"customerId", "customer_id"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Merged"), #"Trimmed Text" = Table.TransformColumns(#"Merged Columns",{{"Merged", Text.Trim, type text}}), #"Split Column by Delimiter" = Table.SplitColumn(#"Trimmed Text", "Merged", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Merged.1", "Merged.2"}), #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Merged.1", Int64.Type}, {"Merged.2", Int64.Type}}), #"Filtered Rows4" = Table.SelectRows(#"Changed Type2", each true), #"Inserted Week of Year" = Table.AddColumn(#"Filtered Rows4", "Week of Year", each Date.WeekOfYear([createdAt]), Int64.Type) in #"Inserted Week of Year"