I recently wrote about an approach to dynamically load CSV files to Power BI through Power Query without the engine to clutter up the UI in Power BI with helper queries. You can read it here. The approach also had some functionallity for getting the first file and traversing the headers automatically. This has now been re-coded to fit a folder from your source containing JSON files. The JSON file structure is somewhat easier to handle, as it is using the Json.
Have you also seen alot of “Helper Queries” in Power Query when working with files from folders? I think it is very cluttered to have all these helpers laying around in the Power Query editor. What I’m used to see Above is the usual way of Power Query to handle several files in the same folder. The approach is done to have only 1 (usually the first) file to create the schema from.
The Power Query tool has just been updated and there are some pretty neat features rolled out: New Transfomation: Use Headers as First Row. Column Name field in Merge Columns dialog. Quick Access Toolbar in Query Editor dialog. Warning for stale data previews in new queries. Function Invocation from Search. Preserve Excel Number formats in the worksheet on query refresh. Along side this update – there is also an updated Salesforce connector for Power Query which you need to install after installing the above update.