'Unable to bring field into Power BI correctly using M Code

I'm trying to bring data into Power BI via Power Query. The problem is that one field contains a colon that Power BI is seeing as a field delimiter and is needlessly splitting the field. The field "Fiscal_Period" should read "FY20: M05 - Mar 2020" but is being brought in as two separate fields that look like:

result.fiscal_period.link result.fiscal_period.value https://xxxxxxx.com/api/now/table/fiscal_period/d8b777631b05d01064418550cd4bcb69 d8b777631b05d01064418550cd4bcb69

I need to bring the field in as a text but don't seem to be able to convert it using M Code.

The Code I have is:

... let

  Source = Json.Document(Web.Contents(https://xxxxxxx.com/api/now/table/benefit_plan_breakdown)),

  #"Converted to table" = Table.FromRecords({Source}),

  #"Expanded result" = Table.ExpandListColumn(#"Converted to table", "result"),

  #"Expanded result1" = Table.ExpandRecordColumn(#"Expanded result", "result", {"benefit_plan", "breakdown_type", "exchange_rate", "entered_currency", "sys_mod_count", "program", "reference_rate", "sys_updated_on", "sys_tags", "sys_id", "actual_benefit", "estimated_benefit", "sys_updated_by", "task", "portfolio", "variance", "fiscal_period", "sys_created_on", "exchange_rate_date", "sys_domain", "base_estimated_benefit", "sys_created_by"}, {"result.benefit_plan", "result.breakdown_type", "result.exchange_rate", "result.entered_currency", "result.sys_mod_count", "result.program", "result.reference_rate", "result.sys_updated_on", "result.sys_tags", "result.sys_id", "result.actual_benefit", "result.estimated_benefit", "result.sys_updated_by", "result.task", "result.portfolio", "result.variance", "result.fiscal_period", "result.sys_created_on", "result.exchange_rate_date", "result.sys_domain", "result.base_estimated_benefit", "result.sys_created_by"}),

  #"Expanded result.fiscal_period" = Table.ExpandRecordColumn(#"Expanded result1", "result.fiscal_period", {"link", "value"}, {"result.fiscal_period.link", "result.fiscal_period.value"})

in

  #"Expanded result.fiscal_period"

...

Any suggestions most welcome.



Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source