Import folder of JSON files to expand and append all into one table

[michellepace]

Hi there,

I can import one JSON file without a problem and tranform it into one flat table. Now I need to import ALL json files in a folder, expand then and then append them all into one table (with a new column that says which file each row came from). Exactly like a folder of CSV files work. The problem is that the JSON files need to expand and then I loose the originating source file name. I have been looking at this for 2.5 hours and I have no idea what to do. Please could someone look at my attached file and help me get my 3 json files into one table (with the additional column for filemane).

To keep things simple, I’ve uploaded an excel file as I am stuck in Power Query anyhow. These are the steps I am doing:

Tried "Transform Data"
Steps: Excel > Open Power Query > New Source > File > Folder > select my json folder > select Transform Data… Click on “Binary” in first column
Result: Get a list of Json records, I can expand them but I loose my filename

Tried "Combine and Transform Data"
Steps: Excel > Open Power Query > New Source > File > Folder > select my json folder > select Combine and Transform Data… Click on “Binary” in first column
Result: I get errors everywhere. I have tried to unpick this myself, but I don’t understand what I am looking at. Also, I have googled and googled - and haven’t been able to work it out.

.
.

Question: Can someone please guide me as to how load Json files from a folder so that the end result is one flat table with an additional column showing the origination file name? I have attached the 3 JSON files I am trying to combine: json-files.zip (3.6 KB)

Thanks in adance, Michelle

[AntrikshSharma]

@michellepace Don’t click on the binary instead click on the expand option.

[michellepace]

Hi @AntrikshSharma - lovely to hear from you again, thank you for your reply. I tried doing what you recommended but I’m still getting errors. Could you please attach your excel which you screenshot?

Edit: Here is a 1 minute recording of trying to follow your instructions: https://youtu.be/tum8kO84uGM (tag @AntrikshSharma )

Thanks again
Michelle

[AntrikshSharma]

@michellepace It is good to hear from you too after so long!

I have investigated and it looks like Power Query in Excel is stupid and is using wrong function for converting a List to Table.

Instead it should be Table.FromList

So instead of trying to fix this(unless you absolutely want to) what you can do is build your query in PBI and then once you are done copy everything from PBI’s Queries Pane to Excel’s Queries Pane.

[EnterpriseDNA]

Hi @michellepace , did the response provided by @AntrikshSharma help you solve your query?

If not, how far did you get and what kind of help you need further?

If yes, kindly mark as solution the answer that solved your query.

[EnterpriseDNA]

Hi @michellepace, we’ve noticed that no response has been received from you since October 16.

We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.

[michellepace]

Hi @AntrikshSharma , thanks very much for your reply. I have found a much easier way - - - just three little clicks as shown below and I am good to go to the races!

Thank you nonetheless though