How to deal with Irregular columns in JSON from REST API

[amilpbi]

Beginner to power bi and Rest Api,
We have this REST API from client that is connected to Sql Server to fetch user data demographics(like responedent age, respondent gender, etc..) and JSON model(containing columns of Responedent Responses like whom did you vote for Elections, Whom did you vote last time and so on) from MongoDB. 

The client hopes to visualise the following:
1. Identify groups of people that could align politically, people that completely oppose the party, Neither support or oppose.
2. Find their political alignment age group wise, Gender wise, ethnicity wise.

3. Find Swing or Retainship over the years and predict the likeness of individual to swing or retain based on the data.

4. Identify schemes by Govt that people liked or hated.

5. Election Candidate review over the years and his rank over other candidates from the given area(s).
How do I preprocess this? Transform into something useful and then make Visuals out of this?
Only 6-10 columns are common, every other column is an amalgamation of various political questions asked since 2007 which just gives 80% null values for these irregular columns. 
I am absolutely underprepared for this task, The client has PPU license and wouldn't probably invest into any data warehouse or Azure Data Factory preprocessing step to make sense out of this data.
The data is around 50 million in size, few individuals have been observed over the years but most have responded only once. 

[Anonymous · Accepted solution]

Thanks for the reply from rajendraongole1 , please allow me to provide another insight:

Hi, @amilpbi 
Thanks for reaching out to the Microsoft fabric community forum.

1.Here is an example of how I converted JSON data into a table, which I hope will be helpful to you:

1.Here is an example of how I converted JSON data into a table, which I hope will be helpful to you:
let
    Source = Json.Document("{""EventId"":12345,""EventType"":""Error"",""IsCritical"":1,""StartTime"":""2023-10-01T12:00:00"",""EndTime"":""2023-10-01T13:30:00"",""DurationMinutes"":90}"),
    Table = Table.FromRecords({
        [EventId = Source[EventId], EventType = Source[EventType], IsCritical = Source[IsCritical], StartTime = Source[StartTime], EndTime = Source[EndTime], DurationMinutes = Source[DurationMinutes]]
    }),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(Table, {}, "Attribute", "Value")
in
    #"Unpivoted Columns"

2. Secondly, here is the official documentation for processing data in Power Query:

Common tasks include:

Removing duplicates:

Working with duplicate values - Power Query | Microsoft Learn

Changing data types:

Data types in Power BI Desktop - Power BI | Microsoft Learn

3.Next, regarding visualization, you can refer to the following:

For political inclination analysis, you can create a pie chart to show the proportions of support, opposition, and neutrality. This allows you to visually see the distribution of different political inclinations.

For age group analysis, you can use a stacked bar chart to show the distribution of different political inclinations by age group. This allows you to compare the political inclinations of different age groups.

For trend analysis, you can use a line chart to show the trend of swing or stable inclinations over different years. This allows you to observe the changes in political inclinations over time.

For more details, please refer to:

Combo chart in Power BI - Power BI | Microsoft Learn

Doughnut charts in Power BI - Power BI | Microsoft Learn

Key influencers visualizations tutorial - Power BI | Microsoft Learn

 
Of course, if you have any new discoveries or questions, please feel free to get in touch with us.
 

Best Regards,

Leroy Lu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[rajendraongole1]

Hi @amilpbi  - Use Power BI's REST API connector to access the data. If needed, use Postman to test the API and understand its response structure before integrating into Power BI.
For MongoDB JSON responses, Power BI's JSON connector can flatten nested structures. Transform irregular columns into a more uniform structure, such as Question-Response pairs.

GitHub - microsoft/PowerBI-CSharp: Welcome to the .NET developer community for Power BI. Here you wi...

Handle Null Values:

• Remove columns with consistently high null percentages (e.g., >80% nulls).
• Use DAX or Power Query to fill missing values where possible or use "Unknown" categories.

• Filter Relevant Data:

  Focus on respondents who answered core questions over time (e.g., voting history, candidate reviews).
  For demographics, ensure consistency with common identifiers (e.g., Respondent ID).
  Aggregate Data:

  Use Power Query or SQL to create aggregated tables for analysis, such as:
  Age group vs. political alignment.
  Year vs. voting patterns.