Forum Discussion
FHIR Questionnaires - flattening the nested Item structure
Hello -
Note - I have not worked with FHIR but have done similar projects.
These pages should get you going with regards to dynamically expanding all nested tables/objects (aka flattening). You will likely need to customize to your specific situation, but it should be relatively easy to do.
https://pivotalbi.com/dynamically-flatten-a-parent-child-hierarchy-using-power-query-m/
In terms of shaping the data in a way that can be visualized, after your data has been flattened, I recommend normalizing the data as much as possible, come up with some attributes/dimensions and add them via conditional columns, such as Category, Subcategory, etc. If your data includes values that should be calculated (sums, averages, etc.), group on the attributes you created plus other relevant columns, then unpivot the calculated columns and create a data types column, like currency, integer and percent. Create dimension tables that you can use in slicers/table visuals. Create a few of measures in the data model - one each for currency values, integer values and percent values (so you can assign formatting appropriately).
In this snip below, the text in the Measures column are column names that I normalized.
Here is a snip of a report page - instead of slicers, I used tables.
Hello -
Note - I have not worked with FHIR but have done similar projects.
These pages should get you going with regards to dynamically expanding all nested tables/objects (aka flattening). You will likely need to customize to your specific situation, but it should be relatively easy to do.
https://pivotalbi.com/dynamically-flatten-a-parent-child-hierarchy-using-power-query-m/
In terms of shaping the data in a way that can be visualized, after your data has been flattened, I recommend normalizing the data as much as possible, come up with some attributes/dimensions and add them via conditional columns, such as Category, Subcategory, etc. If your data includes values that should be calculated (sums, averages, etc.), group on the attributes you created plus other relevant columns, then unpivot the calculated columns and create a data types column, like currency, integer and percent. Create dimension tables that you can use in slicers/table visuals. Create a few of measures in the data model - one each for currency values, integer values and percent values (so you can assign formatting appropriately).
In this snip below, the text in the Measures column are column names that I normalized.
Here is a snip of a report page - instead of slicers, I used tables.