Integrasjon med Power BI Desktop

I forbindelse med nytt Public API, så har enkelte endepunkt en begrensning på mengde data som kan hentes ut pr. kall. Derfor er det innført paginering på disse endepunktene. For de som bruker Power BI for å hente ut data må man derfor endre disse uthentingene av data for å ivareta paginering. Det er ikke en stor operasjon. 

For å løse dette brukes Power Query M som er Microsoft sitt «kodespråk» som ikke krever noe kodekunnskap. Dette brukes mye i eksempelvis Power BI og Excel. 

I dette eksempelet skal vi hente ut data fra timelisten, altså registrerte timelinjer. Vi bruker APIet Timelinje og endepunktet GetTimelinje for dette. Dette er dokumentert på https://developer.tidsbanken.net/api-details#api=api-timelinje. 

I dag er det en begrensing på 1000 objekter pr. side. Dette er gjenstand for endring oppover uten varsel, men vi ikke ha noen praktisk betydning så lenge man holder seg til pagineringen det er lagt opp til og ikke gjør paginering manuelt. Ved håndtering av paginering manuelt vil endringen ikke ha noen konsekvenser annet enn at det gir mulighet for å hente ut flere objekter pr. kall. 

Dette er kun mulig å gjøre i Power BI Desktop for Windows. 

1. Koble til APIet 

Først må vi koble oss til APIet for å verifisere at vi har tilgang til dataene. Dette gjør vi på følgende måte.  

1.2. Åpne Power BI og klikk på Hent data og velg Web: 
 

1.2. I pop'up-vinduet velger vi Avansert og legger inn URLen til endepunktet og api-nøkkel, subscription key og x-api-version som headers. 

I eksempelt har vi valgt å legge $select og $top på egne linjer for å få best mulig oversikt. Vi har valgt her å hente ut Id, AnsattId, Dato og AntallTImer for å ha noe data å se på videre. Senere skal spørringen hente samtlige felter fra APIet. Vi har også lagt inn en topp 1000 i første omgang.

https://api.tidsbanken.net/timelinje/TimelinjeBehandlet? 
$select=Id, AnsattId, Dato, AntallTimer 
&$top=2000 
tb-key: xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx 
Ocp-Apim-Subscription-Key: xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx  
x-api-version: 3.0 
 
Ocp-Apim-Subscription-Key får du ved å opprette en bruker på developer.tidsbanken.net. Tb-key får du som administrator fra Tidsbanken support. Send epost til support@tidsbanken.no
 
Klikk OK. 
 
 1.2.1. Hvis vi ikke har koblet oss til Tidsbanken sitt API tidligere vil du få opp et popup-vindu som spør om du vil koble til:

 
 Klikk på Koble til. 
 
 1.3. Vi skal nå motta en respons fra APIet med de 1000 første objektene/radene. 
 
Det er to egne kolonner med tittelen @odata.context og @odata.nextLink. I responsen fra APIet kommer alle de 1000 objektene inne i et array som heter value. Derfor står alle øvrige kolonnenavn med eksempelvis value.Id. @odata.context og @odata.nextLink er egne objekter i responsen og Power BI vil derfor liste ut dette på hvert objekt som vi ser i listen. 
 

2.Legge inn paginering 

I responsen fra APIet får vi en lenke (@odata.nextLink) til neste side med de neste 1000 objektene vi spør etter. Power BI kan spørre etter de neste 1000 slik at vi slipper å gjøre dette selv. 

2.1. Klikk på Avansert redigering i menyen: 

Her kommer det et popup-vindu for å endre på spørringen:

 
2.2. Her erstatter vi alt som står der og legger inn følgende kodesnutt: 
(Merk at tb-key og Ocp-Apim-Subscription-Key må endres til dine egne).

let 
BaseUrl = "https://api.tidsbanken.net/timelinje/TimelinjeBehandlet?$select=Id, AnsattId, Dato, AntallTimer&$top=200", 
getJson = (url) as record => 
let 
source = Json.Document(Web.Contents(url, [Headers=[#"tb-key"="xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx", #"x-api-version"="3.0", #"Ocp-Apim-Subscription-Key"="xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx"]])), 
data = source[value], 
next = Record.FieldOrDefault(source, "@odata.nextLink", null), 
output = [Data=data, Next=next] 
in 
output, 
generateData = (url) => 
let 
init = getJson(url), 
output = List.Generate(()=>init, each _ <> null, each if _[Next] <> null then getJson(_[Next]) else null, each _[Data]) 
in 
output, 
data = generateData(BaseUrl), 
table = Table.FromList(data, Splitter.SplitByNothing(), null, null, ExtraValues.Error), 
expandedTable = Table.ExpandRecordColumn(Table.ExpandListColumn(table, "Column1"), "Column1",  
{ 
"Id", "AnsattId", "Dato", "AntallTimer" 
},  
{ 
"Id", "AnsattId", "Dato", "AntallTimer" 
} 
) 
in 
expandedTable 

Dette vil da gjøre spørring for de neste 1000 objektene helt til det ikke finnes noen @odata.nextLink i responsen.  
 
Klikk Fullfør. 

Tabellen vil nå flylles med alle dataene fra API'et i kolonnene Id, AnsattId, Dato og AntallTimer.  

3. Filtrering med dynamisk dato 

Hvis du skal bruke denne spørringen til å hente de siste 30 dagene med data kan du  legge inn et filter i spørringen. I dette eksempelet vil jeg hente kun timelinjer som har dato innenfor de siste 30 dagene, altså de siste 30 dagers timelinjer. 
 

3.1. Klikk på Avansert redigering i menyen.  
Erstatt eksisterende kode og legg inn følgende kodesnutt: 
(Merk at tb-key og Ocp-Apim-Subscription-Key må endres til dine egne) 

let 
BaseUrl = "https://api.tidsbanken.net/timelinje/TimelinjeBehandlet?$select=Id, AnsattId, Dato, AntallTimer&$top=200&$filter=Dato gt " & Date.ToText(Date.AddDays(DateTime.Date(DateTime.LocalNow()), -30), "yyyy-MM-dd"), 
getJson = (url) as record => 
let 
source = Json.Document(Web.Contents(url, [Headers=[#"tb-key"="xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx", #"x-api-version"="3.0", #"Ocp-Apim-Subscription-Key"="xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx"]])), 
data = source[value], 
next = Record.FieldOrDefault(source, "@odata.nextLink", null), 
output = [Data=data, Next=next] 
in 
output, 
generateData = (url) => 
let 
init = getJson(url), 
output = List.Generate(()=>init, each _ <> null, each if _[Next] <> null then getJson(_[Next]) else null, each _[Data]) 
in 
output, 
data = generateData(BaseUrl), 
table = Table.FromList(data, Splitter.SplitByNothing(), null, null, ExtraValues.Error), 
expandedTable = Table.ExpandRecordColumn(Table.ExpandListColumn(table, "Column1"), "Column1",  
{ 
"Id", "AnsattId", "Dato", "AntallTimer" 
},  
{ 
"Id", "AnsattId", "Dato", "AntallTimer" 
} 
) 
in 
expandedTable

Klikk Fullfør.  

Tabellen fylles nå med timelinjer som har dato siste 30 dager. Tilsvarende metode kan brukes på andre dato-/tidspunktfelt. Filteret kan også kombineres med andre elementer du ønsker å filtrere på som ArtId (lønnsart), AvdelingId eller ProsjektId for å nevne noen. Her er et eksempel på dynamisk datofilter kombinert med AvdelingId:   
 
BaseUrl = "https://api.tidsbanken.net/timelinje/TimelinjeBehandlet?$select=Id, AnsattId, Dato, AntallTimer, AvdelingId&$top=200&$filter=AvdelingId eq '100' and Dato gt " & Date.ToText(Date.AddDays(DateTime.Date(DateTime.LocalNow()), -30), "yyyy-MM-dd"), 

 
Husk å sjekke typen på verdien du skal filtrere på. I dette eksempelet bruker vi ‘ (single quotes) rundt avdelingsnummeret da dette er en string/tekst. For ansattnummer brukes ikke ‘ da dette er et tallfelt. 

4. Hente ut flere felter 

For å kunne få ut flere felter er det bare å legge til i $select i BaseUrl og i expandedTable. Her er det vist med å legge til AvdelingId: 
 

BaseUrl = "https://api.tidsbanken.net/timelinje/TimelinjeBehandlet?$select=Id, AnsattId, Dato, AntallTimer, AvdelingId&$top=200&$filter=Dato gt " & Date.ToText(Date.AddDays(DateTime.Date(DateTime.LocalNow()), -30), "yyyy-MM-dd"), 
 

og  

expandedTable = Table.ExpandRecordColumn(Table.ExpandListColumn(table, "Column1"), "Column1",  
{ 
"Id", "AnsattId", "Dato", "AntallTimer", "AvdelingId" 
},  
{ 
"Id", "AnsattId", "Dato", "AntallTimer", "AvdelingId" 
} 
) 

5. Hente ut alle felter 

For å hente ut alle feltene som er tilgjengelig i APIet er det bare å legge til feltene i $select og expandedTable som vist i forrige punkt. Bruker du $select=* vil APIet omgjøre dette til å inneholde alle felt. 
I eksempelet kan du se hvordan: 
(Merk at tb-key og Ocp-Apim-Subscription-Key må endres til dine egne) 

let 
    BaseUrl = "https://api.tidsbanken.net/timelinje/TimelinjeBehandlet?$select=Id, AnsattId, Dato, FraKlokken, TilKlokken, VirkeligFraKlokken, VirkeligTilKlokken, Pause1FraKlokken, Pause1TilKlokken, Pause2FraKlokken, Pause2TilKlokken, ArtId, AntallTimer, Kostpris, Salgspris, AvdelingId, ProsjektId, ProsjektLinjeId, AktivitetId, Notat, KontoId, ArbeidsTypeId, SumPauser, Faktureres, FakturaTimeAntall, FakturaNotat, Element1Id, Element2Id, ProduktId, AnleggId, Godkjent, PlanFraKlokken, PlanTilKlokken, ForventetArbeidstidForDag, Produktiv, OpprettetDato, OpprettetAvId, EndretDato, EndretAvId&$top=200&$filter=Dato gt " & Date.ToText(Date.AddDays(DateTime.Date(DateTime.LocalNow()), -30), "yyyy-MM-dd"), 
  
    getJson = (url) as record => 
        let 
            source = Json.Document(Web.Contents(url, [Headers=[#"tb-key"=" xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx", #"x-api-version"="3.0", #"Ocp-Apim-Subscription-Key"=" xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx"]])), 
            data = source[value], 
            next = Record.FieldOrDefault(source, "@odata.nextLink", null), 
            output = [Data=data, Next=next] 
        in 
            output, 
  
    generateData = (url) => 
        let 
            init = getJson(url), 
            output = List.Generate(()=>init, each _ <> null, each if _[Next] <> null then getJson(_[Next]) else null, each _[Data]) 
        in 
            output, 
  
    data = generateData(BaseUrl), 
    table = Table.FromList(data, Splitter.SplitByNothing(), null, null, ExtraValues.Error), 
    expandedTable = Table.ExpandRecordColumn(Table.ExpandListColumn(table, "Column1"), "Column1",  
        { 
            "Id", "AnsattId", "Dato", "FraKlokken", "TilKlokken", "VirkeligFraKlokken", "VirkeligTilKlokken",  
            "Pause1FraKlokken", "Pause1TilKlokken", "Pause2FraKlokken", "Pause2TilKlokken", "ArtId", "AntallTimer",  
            "Kostpris", "Salgspris", "AvdelingId", "ProsjektId", "ProsjektLinjeId", "AktivitetId", "Notat", "KontoId",  
            "ArbeidsTypeId", "SumPauser", "Faktureres", "FakturaTimeAntall", "FakturaNotat", "Element1Id", "Element2Id",  
            "ProduktId", "AnleggId", "Godkjent", "PlanFraKlokken", "PlanTilKlokken", "ForventetArbeidstidForDag",  
            "Produktiv", "OpprettetDato", "OpprettetAvId", "EndretDato", "EndretAvId" 
        },  
        { 
            "Id", "AnsattId", "Dato", "FraKlokken", "TilKlokken", "VirkeligFraKlokken", "VirkeligTilKlokken",  
            "Pause1FraKlokken", "Pause1TilKlokken", "Pause2FraKlokken", "Pause2TilKlokken", "ArtId", "AntallTimer",  
            "Kostpris", "Salgspris", "AvdelingId", "ProsjektId", "ProsjektLinjeId", "AktivitetId", "Notat", "KontoId",  
            "ArbeidsTypeId", "SumPauser", "Faktureres", "FakturaTimeAntall", "FakturaNotat", "Element1Id", "Element2Id",  
            "ProduktId", "AnleggId", "Godkjent", "PlanFraKlokken", "PlanTilKlokken", "ForventetArbeidstidForDag",  
            "Produktiv", "OpprettetDato", "OpprettetAvId", "EndretDato", "EndretAvId" 
        } 
    ) 
in 
    expandedTable 
 

Klikk Fullfør. 

Tabellen fylles nå med alle feltene som er spurt etter. 

6. Endre typene i responsen 

For å kunne bruke dataene i Power BI på en ryddig måte kan typene endres slik at tekstfelt er tekstfelt, nummer er nummer og datoer er datoer. Dette kan gjøres senere i datasettet i Power BI, men det kan også gjøres direkte i kode, som vist her. I dette eksempelet henter vi ut alle feltene fra APIet og gjør om typene for best mulig datakvalitet. Merk at dette kan ha påvirkning på hvordan Power BI behandler data fra det nye APIet hvis dette ikke er tatt hensyn til tidligere. 

(Merk at tb-key og Ocp-Apim-Subscription-Key må endres til dine egne) 

let 
    BaseUrl = "https://api.tidsbanken.net/timelinje/TimelinjeBehandlet?$select=*&$top=200&$filter=Dato gt " & Date.ToText(Date.AddDays(DateTime.Date(DateTime.LocalNow()), -30), "yyyy-MM-dd"), 
  
    getJson = (url) as record => 
        let 
            source = Json.Document(Web.Contents(url, [Headers=[#"tb-key"=" xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx", #"x-api-version"="3.0", #"Ocp-Apim-Subscription-Key"=" xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx"]])), 
            data = source[value], 
            next = Record.FieldOrDefault(source, "@odata.nextLink", null), 
            output = [Data=data, Next=next] 
        in 
            output, 
  
    generateData = (url) => 
        let 
            init = getJson(url), 
            output = List.Generate(()=>init, each _ <> null, each if _[Next] <> null then getJson(_[Next]) else null, each _[Data]) 
        in 
            output, 
  
    data = generateData(BaseUrl), 
    table = Table.FromList(data, Splitter.SplitByNothing(), null, null, ExtraValues.Error), 
    expandedTable = Table.ExpandRecordColumn(Table.ExpandListColumn(table, "Column1"), "Column1",  
        { 
            "Id", "AnsattId", "Dato", "FraKlokken", "TilKlokken", "VirkeligFraKlokken", "VirkeligTilKlokken",  
            "Pause1FraKlokken", "Pause1TilKlokken", "Pause2FraKlokken", "Pause2TilKlokken", "ArtId", "AntallTimer",  
            "Kostpris", "Salgspris", "AvdelingId", "ProsjektId", "ProsjektLinjeId", "AktivitetId", "Notat", "KontoId",  
            "ArbeidsTypeId", "SumPauser", "Faktureres", "FakturaTimeAntall", "FakturaNotat", "Element1Id", "Element2Id",  
            "ProduktId", "AnleggId", "Godkjent", "PlanFraKlokken", "PlanTilKlokken", "ForventetArbeidstidForDag",  
            "Produktiv", "OpprettetDato", "OpprettetAvId", "EndretDato", "EndretAvId" 
        },  
        { 
            "Id", "AnsattId", "Dato", "FraKlokken", "TilKlokken", "VirkeligFraKlokken", "VirkeligTilKlokken",  
            "Pause1FraKlokken", "Pause1TilKlokken", "Pause2FraKlokken", "Pause2TilKlokken", "ArtId", "AntallTimer",  
            "Kostpris", "Salgspris", "AvdelingId", "ProsjektId", "ProsjektLinjeId", "AktivitetId", "Notat", "KontoId",  
            "ArbeidsTypeId", "SumPauser", "Faktureres", "FakturaTimeAntall", "FakturaNotat", "Element1Id", "Element2Id",  
            "ProduktId", "AnleggId", "Godkjent", "PlanFraKlokken", "PlanTilKlokken", "ForventetArbeidstidForDag",  
            "Produktiv", "OpprettetDato", "OpprettetAvId", "EndretDato", "EndretAvId" 
        } 
    ), 
    transformedTable = Table.TransformColumnTypes(expandedTable,  
        { 
            {"Dato", type datetimezone}, {"FraKlokken", type datetimezone}, {"TilKlokken", type datetimezone},  
            {"VirkeligFraKlokken", type datetimezone}, {"VirkeligTilKlokken", type datetimezone},  
            {"Pause1FraKlokken", type datetimezone}, {"Pause1TilKlokken", type datetimezone},  
            {"Pause2FraKlokken", type datetimezone}, {"Pause2TilKlokken", type datetimezone},  
            {"PlanFraKlokken", type datetimezone}, {"PlanTilKlokken", type datetimezone},  
            {"OpprettetDato", type datetimezone}, {"EndretDato", type datetimezone},  
            {"AntallTimer", type number}, {"Kostpris", type number}, {"Salgspris", type number},  
            {"SumPauser", type number}, {"FakturaTimeAntall", type number}, {"ForventetArbeidstidForDag", type number},  
            {"Faktureres", type logical}, {"Godkjent", type logical}, {"Produktiv", type logical},  
            {"Id", Int64.Type}, {"AnsattId", Int64.Type}, {"OpprettetAvId", Int64.Type}, {"EndretAvId", Int64.Type} 
        } 
    ) 
in 
    transformedTable 

 Klikk Fullfør. 

Nå vil alle feltene settes til «riktige» typer for håndtering videre i Power BI. Dette ser vi på ikonet til venstre for kolonnetittelen. 

7. Antall i $top 

Når vi skal hente ut mye data er det viktig å legge inn en $top i baseURL som er tilstrekkelig slik at vi får med alle dataene vi trenger. Grunnen til at vi har påtvunget $top er for å redusere risiko for å hente ut ekstreme mengder data ved et uhell. 

Eksempel: 

En bedrift med 50 ansatte som bytter jobber 3 ganger om dagen vil ha 4.500 timelinjer på 30 dager.  50 ansatte x 3 timelinjer x 30 dager = 4.500. 

Men vi må huske på å ta høyde for tillegg som genereres, overtid og kanskje andre faktorer som påvirker antall timelinjer. I dette tilfellet kunne eksempelvis en $top=10000 blitt lagt inn. 

Det er også andre faktorer som påvirker antall timelinjer negativt også. Dette kan være eksempelvis timetype som forteller om det er normaltid, overtid eller konvertert overtid (tidskonto). Hvis du bare skal hente ut timelinjer med overtid legger korrekt timetype inn i $filter og $top kan kanskje settes lavere. 

Det viktigste er at det legges inn en $top, men om antallet er 1000 eller 50000 er ikke så viktig.