'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 |
|---|
