Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Create visual in PowerBI from JSON, survey results

Hej all.

 

I'm trying to create a good visual in Power BI, using data from a simple survey we've sent out to our co-workers. The replies are saved in a JSON-file, which I'm importing into PowerBI as a text-file (I keep getting an error when trying to import it as a JSON - not important here).

 

Please see the "raw data" screenshot of what I receive in the raw JSON (importing as text-file), and the "after" screenshot, on what I'm currently struggling with. I'm 100% there must be a much better way of doing this 🙂

 

Survey consists of three questions, all optional:
SurveyResRating = This is the co-workers overall rating, 1-5

SurveyReswasItHelpful = This is a yes (1) - no (2) question if they though the ChatBot was helpful.

feedback = Open question.

 

The JSON I get basically looks like this all the way through:

{ "SurveyResRating": "5",
"SurveyReswasItHelpful": "2",
"feedback": "support is good for me although my issue has not been resolved"
}

{ "SurveyResRating": "3",
"SurveyReswasItHelpful": "2"
}

 

As the survey is obscure enough and anonymous, I'm attaching the raw data as well in .txt-format.

 

Below is what I'm after.

 

RatingWas ChatBot helpful?Feedback
#Yes/notext
Total--
Need Help
  • V-lianl-msft's avatar
    V-lianl-msft
    4 years ago

    Hi Anonymous ,

     

    To import JSON files, click the “Get Data” tab from the top menu. From the list of data sources, select “JSON”  instead of text/csv

     

    Test json as below:

    [
    {
        "SurveyResRating": "5",
        "SurveyReswasItHelpful": "2",
        "feedback": "ssssssssssssssss"
        
    },
    {
        "SurveyResRating": "3",
        "SurveyReswasItHelpful": "2"
        
       
    },
    {
        "SurveyResRating": "7",
        "SurveyReswasItHelpful": "1",
        "feedback": "ggg"
        
    }
]

     


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

2 Replies

  • V-lianl-msft's avatar
    V-lianl-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi Anonymous ,

     

    To import JSON files, click the “Get Data” tab from the top menu. From the list of data sources, select “JSON”  instead of text/csv

     

    Test json as below:

    [
    {
        "SurveyResRating": "5",
        "SurveyReswasItHelpful": "2",
        "feedback": "ssssssssssssssss"
        
    },
    {
        "SurveyResRating": "3",
        "SurveyReswasItHelpful": "2"
        
       
    },
    {
        "SurveyResRating": "7",
        "SurveyReswasItHelpful": "1",
        "feedback": "ggg"
        
    }
]

     


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

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Thanks V-lianl-msft 

    The test JSON worked like a charm! Also I got a new JSON today from the source (3rd party), and that JSON worked fine after I added brackets around it (" [ ] "). For some reason, I got an error message last week trying to import the JSON. But the new one (with additional answers, survey is still ongoing) works 🙂

     

    Thanks a bunch! Amazing that PowerBI can ready JSON and present it so easily (as long as the JSON is OK).