How to query values only from Azure Blob Storage with Excel Power Query?

Question

I am trying to query some chatbot data from Azure Blob Storage with Excel Power Query. Unfortunately, Excel cells not only contain the value but also the key in each cell.

This is some sample content of a blob in my blobstorage. The data always has the same structure.

{"id":"3398","realId":"3398","document":{"userID":"3398","name":"Testdurchgang","age":18,"gender":"männlich","education":"Diplom","complete":true,"advisoryConversationId":"6EFa4fsLaJhB4U1LlwNksU-f","roundCounter":5,"riskchoices":["A","A","A","A","B","B","B","B","B","B"],"riskAssessmentComplete":true,"riskDescription":"risikoneutral","order":["2","1","0"],"botRecommendation":"Breen GmbH","choice":"ACG GmbH","follow":false,"eTag":"*","resultConversationId":"Kxw9FZ2KwdWKqElSQQ0nG2-f","win1":"none","win2":"ACG GmbH","loss1":"Breen GmbH","loss2":"Plus GmbH","payout":"Du bekommst 6500 Geldeinheiten = 6,50€ ausgezahlt.","payoutNumber":"6,5"}}

This is how the data looks like in the Power Query tool:

As you can see, the first column even contains the bracket from the JSON and column 3 contains the "content" tag. In the best case, I want only the value in each cell. I can set the column description manually if necessary.

Is there a way to achieve this?

I know I could do it with Excel functions but this would not be a really nice solution.

Thank you in advance.

bharathn-msft · Accepted Answer · 2019-03-25 23:04:29Z

2

Make sure the file you have stored in Storage blob container is of .json format.

Was able to upload your sample content into blob as below

{
	"id": "3398",
	"realId": "3398",
	"document": {
		"userID": "3398",
		"name": "Testdurchgang",
		"age": 18,
		"gender": "männlich",
		"education": "Diplom",
		"complete": true,
		"advisoryConversationId": "6EFa4fsLaJhB4U1LlwNksU-f",
		"roundCounter": 5,
		"riskchoices": [
			"A",
			"A",
			"A",
			"A",
			"B",
			"B",
			"B",
			"B",
			"B",
			"B"
		],
		"riskAssessmentComplete": true,
		"riskDescription": "risikoneutral",
		"order": [
			"2",
			"1",
			"0"
		],
		"botRecommendation": "Breen GmbH",
		"choice": "ACG GmbH",
		"follow": false,
		"eTag": "*",
		"resultConversationId": "Kxw9FZ2KwdWKqElSQQ0nG2-f",
		"win1": "none",
		"win2": "ACG GmbH",
		"loss1": "Breen GmbH",
		"loss2": "Plus GmbH",
		"payout": "Du bekommst 6500 Geldeinheiten = 6,50€ ausgezahlt.",
		"payoutNumber": "6,5"
	}
}

In excel was able to import

Click on Binary and should see below

Click on Record and should see the below

Insert into the table should show something as below

Hope the above steps help !

answered Mar 25, 2019 at 23:04

bharathn-msft

1,0206 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

FRANZKAFKA Over a year ago

Your very first sentence was my problem: The data was in json format but the blobs were not specified as .json. By manually uploading a .json file from my harddrive I could replicate the behavior. Unfortunately, this way I can only load the content of a single file into the spreadsheet. By not specifying the extension, I was able to get a table in excel in which each column represents a key and the different values (about 200 per key) are represented below, which is necessary for analyzing the data (e.g. frequency distribution).

bharathn-msft Over a year ago

I understand that this way you can only load the content of a single file. However please check if you can reformat your json files to just have the document section, that might help you load all the files at once.

Collectives™ on Stack Overflow

How to query values only from Azure Blob Storage with Excel Power Query?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related