I'm trying to expand a column. The data is being fetched from a REST API. The column in query editor is Column1.custom_field and contains a 1:1 relationship. In the table it shows the items as "Record"
When i tried to modify the query to expand it out, i'm missing rows from the dataset. What am i doing wrong?
Original Query:
let
BaseUrl = "https://tenant.freshsales.io/api/deals/view/3001120705/?include=owner&",
Token = "***",
EntitiesPerPage = 25,
GetJson = (Url) =>
let Options = [Headers=[ #"Authorization" = "Token token=" & Token ]],
RawData = Web.Contents(Url, Options),
Json = Json.Document(RawData)
in Json,
GetEntityCount = () =>
let Url = BaseUrl & "page=1",
Json = GetJson(Url),
Count = Json[meta][total_pages]
in Count,
GetPage = (Index) =>
let Skip = "page=" & Text.From(Index),
Url = BaseUrl & Skip,
Json = GetJson(Url),
Value = Json[deals]
in Value,
EntityCount = List.Max({ EntitiesPerPage, (GetEntityCount() * 25 ) }),
PageCount = GetEntityCount(),
PageIndices = { 0 .. PageCount - 1 },
Pages = List.Transform(PageIndices, each GetPage(_)),
Entities = List.Union(Pages),
Table = Table.FromList(Entities, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column2" = Table.ExpandRecordColumn(Table, "Column1", {"id", "name", "amount", "base_currency_amount", "expected_close", "closed_date", "stage_updated_time", "custom_field", "probability", "updated_at", "created_at", "deal_pipeline_id", "deal_stage_id", "age", "links", "recent_note", "completed_sales_sequences", "active_sales_sequences", "web_form_id", "upcoming_activities_time", "collaboration", "last_assigned_at", "tags", "last_contacted_sales_activity_mode", "last_contacted_via_sales_activity", "expected_deal_value", "deal_freddy_metrics", "is_deleted", "team_user_ids", "avatar", "fc_widget_collaboration", "forecast_category", "deal_tag", "rotten_days", "owner_id"}, {"Column1.id", "Column1.name", "Column1.amount", "Column1.base_currency_amount", "Column1.expected_close", "Column1.closed_date", "Column1.stage_updated_time", "Column1.custom_field", "Column1.probability", "Column1.updated_at", "Column1.created_at", "Column1.deal_pipeline_id", "Column1.deal_stage_id", "Column1.age", "Column1.links", "Column1.recent_note", "Column1.completed_sales_sequences", "Column1.active_sales_sequences", "Column1.web_form_id", "Column1.upcoming_activities_time", "Column1.collaboration", "Column1.last_assigned_at", "Column1.tags", "Column1.last_contacted_sales_activity_mode", "Column1.last_contacted_via_sales_activity", "Column1.expected_deal_value", "Column1.deal_freddy_metrics", "Column1.is_deleted", "Column1.team_user_ids", "Column1.avatar", "Column1.fc_widget_collaboration", "Column1.forecast_category", "Column1.deal_tag", "Column1.rotten_days", "Column1.owner_id"}),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded Column2",{{"Column1.created_at", type datetimezone}, {"Column1.closed_date", type date}}),
#"Inserted Date" = Table.AddColumn(#"Changed Type", "Date", each DateTime.Date([Column1.created_at]), type date),
#"Renamed Columns" = Table.RenameColumns(#"Inserted Date",{{"Date", "created_at1"}, {"Column1.id", "ID"}, {"Column1.name", "Title"}, {"Column1.amount", "Amount"}, {"Column1.expected_close", "Expected_Close_Date"}, {"Column1.closed_date", "Closed_Date"}, {"Column1.stage_updated_time", "Stage_Updated_Date"}, {"Column1.updated_at", "Record_Updated_Date"}, {"Column1.created_at", "Created_Date"}, {"Column1.deal_stage_id", "Deal_Stage"}, {"Column1.age", "Age"}, {"Column1.recent_note", "Note"}, {"Column1.last_assigned_at", "Last_Assigned_Date"}, {"Column1.last_contacted_sales_activity_mode", "last_contacted_sales_activity_mode"}, {"Column1.last_contacted_via_sales_activity", "last_contacted_via_sales_activity"}, {"Column1.expected_deal_value", "expected_deal_value"}, {"Column1.owner_id", "owner_id"}, {"Column1.rotten_days", "rotten_days"}, {"Column1.completed_sales_sequences", "completed_sales_sequences"}, {"Column1.active_sales_sequences", "active_sales_sequences"}, {"Column1.upcoming_activities_time", "upcoming_activities_time"}, {"Column1.collaboration", "collaboration"}, {"Column1.deal_tag", "deal_tag"}}),
#"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",{"Column1.forecast_category", "Column1.avatar", "Column1.deal_freddy_metrics", "Column1.tags"}),
#"Filtered Rows" = Table.SelectRows(#"Removed Columns", each [Column1.is_deleted] = false)
in
#"Filtered Rows"
Modified Query (Note: Line under #Filtered Rows changed
let
BaseUrl = "https://tenant.freshsales.io/api/deals/view/3001120705/?include=owner",
Token = "***",
EntitiesPerPage = 25,
GetJson = (Url) =>
let Options = [Headers=[ #"Authorization" = "Token token=" & Token ]],
RawData = Web.Contents(Url, Options),
Json = Json.Document(RawData)
in Json,
GetEntityCount = () =>
let Url = BaseUrl & "page=1",
Json = GetJson(Url),
Count = Json[meta][total_pages]
in Count,
GetPage = (Index) =>
let Skip = "page=" & Text.From(Index),
Url = BaseUrl & Skip,
Json = GetJson(Url),
Value = Json[deals]
in Value,
EntityCount = List.Max({ EntitiesPerPage, (GetEntityCount() * 25 ) }),
PageCount = GetEntityCount(),
PageIndices = { 0 .. PageCount - 1 },
Pages = List.Transform(PageIndices, each GetPage(_)),
Entities = List.Union(Pages),
Table = Table.FromList(Entities, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column2" = Table.ExpandRecordColumn(Table, "Column1", {"id", "name", "amount", "base_currency_amount", "expected_close", "closed_date", "stage_updated_time", "custom_field", "probability", "updated_at", "created_at", "deal_pipeline_id", "deal_stage_id", "age", "links", "recent_note", "completed_sales_sequences", "active_sales_sequences", "web_form_id", "upcoming_activities_time", "collaboration", "last_assigned_at", "tags", "last_contacted_sales_activity_mode", "last_contacted_via_sales_activity", "expected_deal_value", "deal_freddy_metrics", "is_deleted", "team_user_ids", "avatar", "fc_widget_collaboration", "forecast_category", "deal_tag", "rotten_days", "owner_id"}, {"Column1.id", "Column1.name", "Column1.amount", "Column1.base_currency_amount", "Column1.expected_close", "Column1.closed_date", "Column1.stage_updated_time", "Column1.custom_field", "Column1.probability", "Column1.updated_at", "Column1.created_at", "Column1.deal_pipeline_id", "Column1.deal_stage_id", "Column1.age", "Column1.links", "Column1.recent_note", "Column1.completed_sales_sequences", "Column1.active_sales_sequences", "Column1.web_form_id", "Column1.upcoming_activities_time", "Column1.collaboration", "Column1.last_assigned_at", "Column1.tags", "Column1.last_contacted_sales_activity_mode", "Column1.last_contacted_via_sales_activity", "Column1.expected_deal_value", "Column1.deal_freddy_metrics", "Column1.is_deleted", "Column1.team_user_ids", "Column1.avatar", "Column1.fc_widget_collaboration", "Column1.forecast_category", "Column1.deal_tag", "Column1.rotten_days", "Column1.owner_id"}),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded Column2",{{"Column1.created_at", type datetimezone}, {"Column1.closed_date", type date}}),
#"Inserted Date" = Table.AddColumn(#"Changed Type", "Date", each DateTime.Date([Column1.created_at]), type date),
#"Renamed Columns" = Table.RenameColumns(#"Inserted Date",{{"Date", "created_at1"}, {"Column1.id", "ID"}, {"Column1.name", "Title"}, {"Column1.amount", "Amount"}, {"Column1.expected_close", "Expected_Close_Date"}, {"Column1.closed_date", "Closed_Date"}, {"Column1.stage_updated_time", "Stage_Updated_Date"}, {"Column1.updated_at", "Record_Updated_Date"}, {"Column1.created_at", "Created_Date"}, {"Column1.deal_stage_id", "Deal_Stage"}, {"Column1.age", "Age"}, {"Column1.recent_note", "Note"}, {"Column1.last_assigned_at", "Last_Assigned_Date"}, {"Column1.last_contacted_sales_activity_mode", "last_contacted_sales_activity_mode"}, {"Column1.last_contacted_via_sales_activity", "last_contacted_via_sales_activity"}, {"Column1.expected_deal_value", "expected_deal_value"}, {"Column1.owner_id", "owner_id"}, {"Column1.rotten_days", "rotten_days"}, {"Column1.completed_sales_sequences", "completed_sales_sequences"}, {"Column1.active_sales_sequences", "active_sales_sequences"}, {"Column1.upcoming_activities_time", "upcoming_activities_time"}, {"Column1.collaboration", "collaboration"}, {"Column1.deal_tag", "deal_tag"}}),
#"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",{"Column1.forecast_category", "Column1.avatar", "Column1.deal_freddy_metrics", "Column1.tags"}),
#"Filtered Rows" = Table.SelectRows(#"Expand CF", each [Column1.is_deleted] = false),
#"Expand CF" = Table.ExpandRecordColumn(#"Filtered Rows", "Column1.custom_field", {"cf_proc_dates", "cf_provider", "cf_tech","cf_mv"})
in
#"Expand CF"
I'm not sure why trying to expand out that record set would result in data missing from the set? Is there an issue with my query?
