Import Multiple json File in Power Query

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 :

Cattura.PNG

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:

 

Cattura.PNG

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

Cattura.PNG

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 :

Cattura.PNG

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 .

Annunci

Rispondi

Inserisci i tuoi dati qui sotto o clicca su un'icona per effettuare l'accesso:

Logo WordPress.com

Stai commentando usando il tuo account WordPress.com. Chiudi sessione / Modifica )

Foto Twitter

Stai commentando usando il tuo account Twitter. Chiudi sessione / Modifica )

Foto di Facebook

Stai commentando usando il tuo account Facebook. Chiudi sessione / Modifica )

Google+ photo

Stai commentando usando il tuo account Google+. Chiudi sessione / Modifica )

Connessione a %s...