Stream: implementers
Topic: Power BI FHIR Connector
Michael Brady (Aug 18 2021 at 16:25):
Hello, I'm following @Michael Hansen Power BI FHIR walkthrough. I'm pulling Patient from my FHIR server. I can see that it generates 5023 unique patients, but when I Expand and Add fields to the Table Visual I can only see the first 100 or so records. I added a Slicer on GivenName and FamilyName and am searching for a patient I know exists (b/c the slicer displays it) and it will not show the record in the Table Visual.
Michael Brady (Aug 18 2021 at 16:25):
https://www.youtube.com/watch?v=AqFZTf_gVhU&t=402s
Michael Brady (Aug 18 2021 at 16:29):
Here is my M:
'let
Source = Fhir.Contents("https://[my server].azurewebsites.net", null),
Patient1 = Source{[Name="Patient"]}[Data],
#"Expanded name" = Table.ExpandTableColumn(Patient1, "name", {"use", "text", "family", "given", "prefix", "suffix", "period"}, {"name.use", "name.text", "name.family", "name.given", "name.prefix", "name.suffix", "name.period"}),
#"Expanded name.given" = Table.ExpandListColumn(#"Expanded name", "name.given"),
#"Expanded name.prefix" = Table.ExpandListColumn(#"Expanded name.given", "name.prefix"),
#"Expanded name.suffix" = Table.ExpandListColumn(#"Expanded name.prefix", "name.suffix"),
#"Expanded telecom" = Table.ExpandTableColumn(#"Expanded name.suffix", "telecom", {"system", "value", "use", "rank", "period"}, {"telecom.system", "telecom.value", "telecom.use", "telecom.rank", "telecom.period"}),
#"Expanded telecom.period" = Table.ExpandRecordColumn(#"Expanded telecom", "telecom.period", {"start", "end"}, {"telecom.period.start", "telecom.period.end"}),
#"Expanded deceased" = Table.ExpandRecordColumn(#"Expanded telecom.period", "deceased", {"boolean", "dateTime"}, {"deceased.boolean", "deceased.dateTime"}),
#"Expanded maritalStatus" = Table.ExpandRecordColumn(#"Expanded deceased", "maritalStatus", {"coding", "text"}, {"maritalStatus.coding", "maritalStatus.text"}),
#"Expanded multipleBirth" = Table.ExpandRecordColumn(#"Expanded maritalStatus", "multipleBirth", {"boolean", "integer"}, {"multipleBirth.boolean", "multipleBirth.integer"}),
#"Expanded maritalStatus.coding" = Table.ExpandTableColumn(#"Expanded multipleBirth", "maritalStatus.coding", {"system", "version", "code", "display", "userSelected"}, {"maritalStatus.coding.system", "maritalStatus.coding.version", "maritalStatus.coding.code", "maritalStatus.coding.display", "maritalStatus.coding.userSelected"}),
#"Expanded communication" = Table.ExpandTableColumn(#"Expanded maritalStatus.coding", "communication", {"language", "preferred"}, {"communication.language", "communication.preferred"}),
#"Expanded communication.language" = Table.ExpandRecordColumn(#"Expanded communication", "communication.language", {"coding", "text"}, {"communication.language.coding", "communication.language.text"}),
#"Expanded communication.language.coding" = Table.ExpandTableColumn(#"Expanded communication.language", "communication.language.coding", {"system", "version", "code", "display", "userSelected"}, {"communication.language.coding.system", "communication.language.coding.version", "communication.language.coding.code", "communication.language.coding.display", "communication.language.coding.userSelected"}),
#"Expanded generalPractitioner" = Table.ExpandTableColumn(#"Expanded communication.language.coding", "generalPractitioner", {"reference", "type", "identifier", "display"}, {"generalPractitioner.reference", "generalPractitioner.type", "generalPractitioner.identifier", "generalPractitioner.display"}),
#"Expanded managingOrganization" = Table.ExpandRecordColumn(#"Expanded generalPractitioner", "managingOrganization", {"reference", "type", "identifier", "display"}, {"managingOrganization.reference", "managingOrganization.type", "managingOrganization.identifier", "managingOrganization.display"}),
#"Expanded address" = Table.ExpandTableColumn(#"Expanded managingOrganization", "address", {"use", "type", "text", "line", "city", "district", "state", "postalCode", "country", "period"}, {"address.use", "address.type", "address.text", "address.line", "address.city", "address.district", "address.state", "address.postalCode", "address.country", "address.period"}),
#"Expanded address.line" = Table.ExpandListColumn(#"Expanded address", "address.line"),
#"Expanded address.period" = Table.ExpandRecordColumn(#"Expanded address.line", "address.period", {"start", "end"}, {"address.period.start", "address.period.end"}),
#"Expanded contact" = Table.ExpandTableColumn(#"Expanded address.period", "contact", {"relationship", "name", "telecom", "address", "gender", "organization", "period"}, {"contact.relationship", "contact.name", "contact.telecom", "contact.address", "contact.gender", "contact.organization", "contact.period"}),
#"Expanded contact.relationship" = Table.ExpandTableColumn(#"Expanded contact", "contact.relationship", {"coding", "text"}, {"contact.relationship.coding", "contact.relationship.text"}),
#"Expanded contact.name" = Table.ExpandRecordColumn(#"Expanded contact.relationship", "contact.name", {"use", "text", "family", "given", "prefix", "suffix", "period"}, {"contact.name.use", "contact.name.text", "contact.name.family", "contact.name.given", "contact.name.prefix", "contact.name.suffix", "contact.name.period"}),
#"Expanded contact.name.given" = Table.ExpandListColumn(#"Expanded contact.name", "contact.name.given"),
#"Expanded contact.name.prefix" = Table.ExpandListColumn(#"Expanded contact.name.given", "contact.name.prefix"),
#"Expanded contact.name.suffix" = Table.ExpandListColumn(#"Expanded contact.name.prefix", "contact.name.suffix"),
#"Expanded contact.name.period" = Table.ExpandRecordColumn(#"Expanded contact.name.suffix", "contact.name.period", {"start", "end"}, {"contact.name.period.start", "contact.name.period.end"}),
#"Expanded contact.telecom" = Table.ExpandTableColumn(#"Expanded contact.name.period", "contact.telecom", {"system", "value", "use", "rank", "period"}, {"contact.telecom.system", "contact.telecom.value", "contact.telecom.use", "contact.telecom.rank", "contact.telecom.period"}),
#"Expanded contact.telecom.period" = Table.ExpandRecordColumn(#"Expanded contact.telecom", "contact.telecom.period", {"start", "end"}, {"contact.telecom.period.start", "contact.telecom.period.end"}),
#"Expanded contact.address" = Table.ExpandRecordColumn(#"Expanded contact.telecom.period", "contact.address", {"use", "type", "text", "line", "city", "district", "state", "postalCode", "country", "period"}, {"contact.address.use", "contact.address.type", "contact.address.text", "contact.address.line", "contact.address.city", "contact.address.district", "contact.address.state", "contact.address.postalCode", "contact.address.country", "contact.address.period"}),
#"Expanded contact.address.line" = Table.ExpandListColumn(#"Expanded contact.address", "contact.address.line"),
#"Expanded contact.address.period" = Table.ExpandRecordColumn(#"Expanded contact.address.line", "contact.address.period", {"start", "end"}, {"contact.address.period.start", "contact.address.period.end"}),
#"Expanded contact.organization" = Table.ExpandRecordColumn(#"Expanded contact.address.period", "contact.organization", {"reference", "type", "identifier", "display"}, {"contact.organization.reference", "contact.organization.type", "contact.organization.identifier", "contact.organization.display"}),
#"Expanded contact.period" = Table.ExpandRecordColumn(#"Expanded contact.organization", "contact.period", {"start", "end"}, {"contact.period.start", "contact.period.end"})
in
#"Expanded contact.period"'
Ranvijay Kumar (Aug 18 2021 at 16:56):
Hi @Michael Brady , can we setup some time to go through your issue? We would like to see what is going on here.
Michael Brady (Aug 18 2021 at 17:03):
@Ranvijay Kumar thanks for your prompt reply. It was an ID10T error. When I was adding fields to my Table Visual PBI automagically set some of them to Sum (birthInteger, telecom.rank). I removed those Summarizations and poof my data returned.
Last updated: Apr 12 2022 at 19:14 UTC
