Extract data from blob url, facing dynamic source error before saving the query

[anusha_madhavan]

Hi, I am trying to create a dataflow in Power BI using the below M query but unable to save it as it states one or more tables references a dynamic data sorce, how to modify? Also, the value in blobUrl is never the same.

let

  Url = "https://api.github.com/enterprises/"&Enterprise&"/copilot/metrics/reports/users-28-day/latest",

  Source = Json.Document(Web.Contents(Url)),

  download_links = Source[download_links],

  blobUrl = download_links{0},

  blobContent = Lines.FromBinary(Web.Contents(blobUrl)),

  blobData = Table.FromColumns({blobContent}, {"RawJson"}),

  parsedJson = Table.TransformColumns(blobData, {"RawJson", Json.Document}),

  expandedData = Table.ExpandRecordColumn(parsedJson, "RawJson", {"report_start_day", "report_end_day", "day", "enterprise_id", "user_id", "user_login", "user_initiated_interaction_count", "code_generation_activity_count", "code_acceptance_activity_count", "generated_loc_sum", "accepted_loc_sum", "totals_by_ide", "totals_by_feature", "totals_by_language_feature", "totals_by_language_model", "totals_by_model_feature", "used_agent", "used_chat"}, {"report_start_day", "report_end_day", "day", "enterprise_id", "user_id", "user_login", "user_initiated_interaction_count", "code_generation_activity_count", "code_acceptance_activity_count", "generated_loc_sum", "accepted_loc_sum", "totals_by_ide", "totals_by_feature", "totals_by_language_feature", "totals_by_language_model", "totals_by_model_feature", "used_agent", "used_chat"}),

  changedTypes = Table.TransformColumnTypes(expandedData, {{"report_start_day", type date}, {"report_end_day", type date}, {"day", type date}, {"enterprise_id", Int64.Type}, {"user_id", Int64.Type}, {"user_login", type text}, {"user_initiated_interaction_count", Int64.Type}, {"code_generation_activity_count", Int64.Type}, {"code_acceptance_activity_count", Int64.Type}, {"generated_loc_sum", Int64.Type}, {"accepted_loc_sum", Int64.Type}, {"totals_by_ide", type any}, {"totals_by_feature", type any}, {"totals_by_language_feature", type any}, {"totals_by_language_model", type any}, {"totals_by_model_feature", type any}, {"used_agent", type logical}, {"used_chat", type logical}}),

  #"Transform columns" = Table.TransformColumnTypes(changedTypes, {{"totals_by_ide", type text}, {"totals_by_feature", type text}, {"totals_by_language_feature", type text}, {"totals_by_language_model", type text}, {"totals_by_model_feature", type text}}),

  #"Replace errors" = Table.ReplaceErrorValues(#"Transform columns", {{"totals_by_ide", null}, {"totals_by_feature", null}, {"totals_by_language_feature", null}, {"totals_by_language_model", null}, {"totals_by_model_feature", null}})

in

  #"Replace errors"

[v-achippa · Accepted solution]

Hi @anusha_madhavan,

Thank you for reaching out to Microsoft Fabric Community.

The issue here is because the power bi dataflows do not allow fully dynamic URLs in Web.Contents. Please use this below M query:

let
Url = "https://api.github.com/enterprises/" & Enterprise & "/copilot/metrics/reports/users-28-day/latest",
Source = Json.Document(Web.Contents("https://api.github.com/", [RelativePath="enterprises/" & Enterprise & "/copilot/metrics/reports/users-28-day/latest"])),
download_links = Source[download_links],
blobUrl = download_links{0},
blobBaseUrl = "https://github.com/",
blobPath = Text.AfterDelimiter(blobUrl, "github.com/"),
blobContent = Lines.FromBinary(Web.Contents(blobBaseUrl, [RelativePath=blobPath])),
blobData = Table.FromColumns({blobContent}, {"RawJson"}),
parsedJson = Table.TransformColumns(blobData, {"RawJson", Json.Document}),

expandedData = Table.ExpandRecordColumn(parsedJson, "RawJson", {"report_start_day", "report_end_day", "day", "enterprise_id", "user_id", "user_login", "user_initiated_interaction_count", "code_generation_activity_count", "code_acceptance_activity_count", "generated_loc_sum", "accepted_loc_sum", "totals_by_ide", "totals_by_feature", "totals_by_language_feature", "totals_by_language_model", "totals_by_model_feature", "used_agent", "used_chat"}, {"report_start_day", "report_end_day", "day", "enterprise_id", "user_id", "user_login", "user_initiated_interaction_count", "code_generation_activity_count", "code_acceptance_activity_count", "generated_loc_sum", "accepted_loc_sum", "totals_by_ide", "totals_by_feature", "totals_by_language_feature", "totals_by_language_model", "totals_by_model_feature", "used_agent", "used_chat"}),

changedTypes = Table.TransformColumnTypes(expandedData, {{"report_start_day", type date}, {"report_end_day", type date}, {"day", type date}, {"enterprise_id", Int64.Type}, {"user_id", Int64.Type}, {"user_login", type text}, {"user_initiated_interaction_count", Int64.Type}, {"code_generation_activity_count", Int64.Type}, {"code_acceptance_activity_count", Int64.Type}, {"generated_loc_sum", Int64.Type}, {"accepted_loc_sum", Int64.Type}, {"used_agent", type logical}, {"used_chat", type logical}}),

#"Transform columns" = Table.TransformColumnTypes(changedTypes, {{"totals_by_ide", type text}, {"totals_by_feature", type text}, {"totals_by_language_feature", type text}, {"totals_by_language_model", type text}, {"totals_by_model_feature", type text}}),
#"Replace errors" = Table.ReplaceErrorValues(#"Transform columns", {{"totals_by_ide", null}, {"totals_by_feature", null}, {"totals_by_language_feature", null}, {"totals_by_language_model", null}, {"totals_by_model_feature", null}})
in
#"Replace errors"

Thanks and regards,

Anjan Kumar Chippa

[v-achippa]

Hi @anusha_madhavan,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution I have provided for the issue worked? or let us know if you need any further assistance.

Thanks and regards,

Anjan Kumar Chippa

[v-achippa]

Hi @anusha_madhavan,

We wanted to kindly follow up to check if the solution I have provided for the issue worked? or let us know if you need any further assistance.

Thanks and regards,

Anjan Kumar Chippa

[v-achippa]

Hi @anusha_madhavan,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution I have provided for the issue worked? or let us know if you need any further assistance.

Thanks and regards,

Anjan Kumar Chippa