When using Power BI often you want to be able to aggregate more information or more data sources in a single query .

Take for example the json file , but this procedure is also valid for other data formats , it often happens that the information is spread across multiple files , and no single document , or is spread over several sites .

What can traditionally do is create a number of queries ( one for each file ) and then merge them together later . Or we can use a simple string of code that allows us to unify them all into a single query that automatically calls the various pages and aggregates .

Let’s start from the beginning .

1. Create a query

The first thing to do is prepare our basic query to the json file. We take any file in json and prepare them as we need to import it into the model . Having done this we go into the rich editor of the query just built and we go to retrieve the script that we have defined , we will show you one example:

let
Origine = Json.Document(Web.Contents(“http:\\file.1231321.json”)),
#”Conversione in tabella” = Record.ToTable(Origine),
#”Tabella Value espansa” = Table.ExpandListColumn(#”Conversione in tabella”, “Value”),
#”Tabella Value espansa1″ = Table.ExpandRecordColumn(#”Tabella Value espansa”, “Value”, {“id”, “team”, “events”}, {“Value.id”, “Value.team”, “Value.events”}),
#”Tabella Value.events espansa” = Table.ExpandListColumn(#”Tabella Value espansa1″, “Value.events”),
#”Tabella Value.events espansa1″ = Table.ExpandRecordColumn(#”Tabella Value.events espansa”, “Value.events”, {“id”, “type”, “x”, “y”, “player”, “endingArea”, “time”}, {“Value.events.id”, “Value.events.type”, “Value.events.x”, “Value.events.y”, “Value.events.player”, “Value.events.endingArea”, “Value.events.time”}),
#”Tabella Value.events.endingArea espansa” = Table.ExpandRecordColumn(#”Tabella Value.events espansa1″, “Value.events.endingArea”, {“x”, “y”, “z”}, {“Value.events.endingArea.x”, “Value.events.endingArea.y”, “Value.events.endingArea.z”}),
#”Aggiunta colonna personalizzata” = Table.AddColumn(#”Tabella Value.events.endingArea espansa”, “Custom”, each if([Value.events.time] – 1442342722)/60 > 45 then (([Value.time] – 1442342722)/60) -15 else ([Value.events.time] – 1442342722)/60),
#”Modificato tipo” = Table.TransformColumnTypes(#”Aggiunta colonna personalizzata”,{{“Custom”, type number}}),
#”Rinominate colonne” = Table.RenameColumns(#”Modificato tipo”,{{“Custom”, “Timing”}})
in
#”Rinominate colonne”

Copy the query just made in notepad so always in the advanced editor delete the entire query and replace with the following string:

Let loadjson =Contents) =>

in loadJSON

This little script transforms our queries power query in a function that can be called from other power query query . But before we close the query power of queries you must be entered within the previous script loadjson everything we saved in notepad , so let’s make a small change.

let’s replace the segunete string of code :

    Origine = Json.Document(Web.Contents(“http:\\file.1231321.json”)),

with this:

    Origine = Json.Document(Web.Contents(contents)),

With this change we’re going to say to power queries that the json file will be contained in the variable contents will be contained the address of the json file. The end result should be the following :

At this point we give a name to our query and save . If everything works properly it should have converted to fuction and if we recall and enter an address you should get the query .

Now back to excel excel and we prepare a multiplication table with a list of all the addresses where are our json file and convert it in the table:

 

we select the table and from powerquery tab select from table:

We create e new column with this formual:

Loadjson([nomecolonnaexcel])

Attention is very important to write the function properly , also count uppercase and lowercase . At this point if you have successfully written the function will get a new column with written inside table for each row :

If we expand the column get the query written previously aggregating But for all the files that we included . If we want to change or add a json file, we simply add to the multiplication table a new line and you’re done .