Load multiple files at a time

[srivallyK_14]

Hi Experts,

I have multiple json files in the folder with same structure. I want to load all the files at a time and create the visualisation. If new files are added to the folder, it should refresh in power bi.  Can anyone help on this.

Regards,
Sree

[PhilipTreacy]

Hi @srivallyK_14 

The column names in that new JSON file don't match the other files, that's why you are getting a null row.

In the main ev query if you click on the Expanded Table Column1 step the main part of the window shows you 2 columns, the name of the JSON file (that the record comes from) and a column of Records

You can click in the column beside the Record to get a preview of the data in the record.  For the file Energy_EV_Meter_11 you can see the column headers are a mixture of upper and lower case

But for the new file Energy_EV_Meter_47 the column names are all upper case.

Power Query is case sensitive.  When the ev query was first created it saw column headers in mixed case.  The new file is all upper case column headers so when the ev query tries to Expand Columns in the last step, it can't find the column names it expects in the new file - and it just creteas null values

The ev query works correctly.

To fix this, the new JSON file must have column names that match all the other files.

Regards

Phil

[srivallyK_14]

Thanks @PhilipTreacy . Now I got the detailed understanding😊

[PhilipTreacy]

Hi @srivallyK_14 

You shouldn't be modifying the Transform Sample File query.  If you need to add transformation steps do it in the main ev query.

The PBIX file I provided has working code that works when you add more files to the folder.

Initially the query was loading 2 JSON files.  The fist had 20 records

and the 2nd had just 1 record

Expanding those tables and then expanding the records inside the tables gives 24 rows of data

Now I added 3 more JSON files to the same directory - I just made copies of the files I had.  The file contents don't matter, this is just for demonstration.

Refreshing the query shows that it is now seeing 5 files - that is correct.

Expanding the tables/records gives 71 rows of data (you can't see them all in this screenshot)

So the query is working to pick up new JSON files correctly.  I don't know what you are doing and why you are having trouble.

If you just use my PBIX and alter the Source step to point to the directory you are using where the JSON files are, it should work

If you are still having issues please provide your PBIX file so I can check the code you are using.  And also provide some screenshots of the data with null values.  And provide copies of these new files that aren't working.

regards

Phil

[srivallyK_14]

@PhilipTreacy I have added transformation steps in main query as you said, then after I have added files in folder. Still I see null values in the new file. I have attached the new json file and pbix file as well for reference.
Steps I have created the pbix file
https://www.dropbox.com/scl/fi/nss6no3uyq9yvqj8rj6iv/Steps.docx?dl=0&rlkey=r8y24sw4640y49wwzgfttzvgu

Link for pbix file:
https://www.dropbox.com/s/zjjf291irsdswkb/Srivally.pbix?dl=0
Link for new json file:
https://www.dropbox.com/s/hcjryr5n3nu51c6/Energy_EV_Meter_47_2021-02-03.json?dl=0https://www.dropbox...

Regards,
Sri

[PhilipTreacy · Accepted solution]

Hi @srivallyK_14 

This is odd, I can't figure out the reason for the error.  I've checked your JSON files and they are valid JSON so I don't know why PQ is generating an error.

I fixed the problem by manuallay editing the Transform Sample File query and it now works.

Please download this PBIX file and test it with your files.

You will need to edit the Source step of the ev query sothat it points to your directory where you have your files. 

Regards

Phil

[srivallyK_14]

@PhilipTreacy what have you done in the pbix file exactly. i could not able to figure out the changes you made. Can you help me on this.

[PhilipTreacy]

Hi @srivallyK_14 

When the query tries to combine the iles the error is generated in the Transform Sample File query

This is caused by the 2nd step in the query which tries to convert the Source step into a table.

So, select the Source step manually from the list of steps, then click on To Table to manually convert the list of records to a table.

It asks if you want to insert a step- say yes.  Then use the default settings to convert to table

You now have a proper table conversion but the step causing the error still exists.  Click on it and delete it.

and everything should work fine.

Regards

Phil

[srivallyK_14]

@PhilipTreacy as we are manually modifying the transform file, we can see data only for first file after extracting to columns, remaining files showing nulls. I have crosschecked the same by doing the same process for excel files which is working fine. but not sure why we are getting error in json file. Please help me on this.

Regards,
Sri

[srivallyK_14]

@PhilipTreacy  If I'm doing like this manually, after adding few steps like datatype change and splitting up columns.Next day any new files were added to the folder, the new file data showing null even though we are having data in that files when we refresh the pbix file.

Regards,
Srivalli

[PhilipTreacy]

Hi @srivallyK_14 

Please supply some sample JSON files,impossible to debug without seeing what you are loading.

regards

Phil

[srivallyK_14]

@PhilipTreacy  attached are the files
https://www.dropbox.com/sh/zxy3ti8umls94my/AABSQf-4qZNuY9FIyjrA7c6ma?dl=0
i can load all the files at a time, it will load in binary format, here i'm converting all the files individually to table which is not going to work if we have more number of files. So i'm looking for doing the process one time for all the files even we have files added later

[amitchandak]

@srivallyK_14 , Have you tried option load data from folder

https://insightsoftware.com/blog/power-bi-load-data-from-folder/

[srivallyK_14]

@amitchandak I'm having json files, it is not allowing me to combinne and load them. gettinng error
https://www.dropbox.com/s/f4otjr9y2y2zt43/LoadError.PNG?dl=0