excel & power bi

Excel 365 en Power BI updates 2021

Zowel Excel als Power BI updates worden continue doorgevoerd. In Excel komen er periodiek functies bij en Power BI wordt elke maand geüpdate.

Excel

De updates in Excel vinden alleen plaats In de Microsoft 365 versie. Het laatste jaar zijn er bijvoorbeeld de functies X.ZOEKEN oftewel XLOOKUP bijgekomen maar ook handige functies als FILTER, UNIEK en SORTEREN. In de deze blog vind je een paar toepassingen.

De belangrijkste nieuwe functie van het afgelopen jaar vind ik X.ZOEKEN deze functie vervangt verticaal zoeken, horizontaal zoeken en de combinatie index / vergelijken en voegt een aantal interessante verbeteringen toe. Deze functie heft alle beperkingen op van VERT.ZOEKEN en voegt zelfs nog wat verbeteringen toe. Zo kan je nu links van de zoekwaarde zoeken, is de exacte match de default setting, kan je de zoekvolgorde aanpassen en een foutmelding inbakken.

De syntax is als volgt: =X.ZOEKEN(zoekwaarde ; zoekbereik ; antwoordbereik ; [”indien niet gevonden”] ; [match methode (exact, kleiner, groter, variabel met * of ?)] ;[ zoekmethode(vanaf eerste, vanaf laatste, binair)] )

Veel mogelijkheden dus waarmee alle tekortkomingen van VLOOKUP wel zo’n beetje weg zijn. Maar pas op: dit werkt natuurlijk niet als je werkbook ook in Excel 2019 of eerder moet worden geopend. Je kunt zelfs zoekwaarden en zoekbereiken combineren met het koppelteken (&). Ga op onderzoek uit en stap over op X.ZOEKEN.

In onderstaan voorbeeld wordt XLOOKUP gebruikt om op basis van 3 zoekwaarden een antwoord uit een tabel op te vragen:

Voorbeeld Excel

Power BI

Werk je met Power BI dan is het goed om de maandelijkse updates bij te houden. De laatste maanden zijn er weer veel interessante opties bijgekomen. Zo kun je nu makkelijk constante lijnen op de x as toevoegen, kun je serie-labels toevoegen aan lijn grafieken en zijn er heel veel extra mogelijkheden bijgekomen voor de DAX functie FORMAT, er is een Power Automate visual toegevoegd, de small multiples optie wordt steeds beter uitgewerkt en natuurlijk elke maand meer en betere visuals en connectoren.

Daarnaast nog een tip voor de weeknummering. Zoals je weet wijkt de Europese weeknummering af van de US weeknummering, en niet alleen op zondag als in de US de nieuwe week al begint. Dit jaar lopen we constant 1 week achter in nummering als gevolg van de NEN/ISO normering die we in Europa hanteren. Lastig als je op weekniveau wilt rapporteren in Power BI. Gebruik het juiste “returntype” namelijk 21 (net als in Excel). Intellisense ondersteunt dit (nog?) niet maar je kunt gewoon 21 invoeren als 2de argument bij de functie WEEKNUM. Het werkt! Zie mijn uitgebreide artikel hierover op LinkedIn.

Tijdens de Power BI training leerde je al hoe je de FORMAT functie kunt gebruiken om in een kalender tabel bijvoorbeeld de maandnaam of weekdag op te nemen. De functie is vergelijkbaar met de functie TEXT in Excel. Om hem te kunnen gebruiken moet je een paar trucjes kennen, namelijk de coderingsopties van de FORMAT functie. Hier een paar voorbeelden:

“m” Het nummer van de maand (als tekst) bijvoorbeeld 2 voor februari
“mm” Het nummer van de maand met voorloop-nul bijvoorbeeld 02 voor februari
“mmm” De afkorting van de maand, bijvoorbeeld “feb” voor februari
“mmmm” De naam van de maand, bijvoorbeeld “februari” voor februari

Voor dagen geldt hetzelfde, gebruik dan de codes “d”, “dd”, “ddd” of “dddd” en bij jaren gebruik je natuurlijk de code “y”.
De syntax ziet er als volgt uit: Month name = FORMAT( [date] , “mmmm” ) of voor een gecombineerd label: Year-Month = FORMAT( [date] , “yyyy-mmm” )

Daar komen veel mogelijkheden bij doordat je FORMAT nu ook kunt gebruiken om LOCALE output op te geven. Dus een datum specifiek in en-GB output of een valuta in elk gewenst format.

Bekijk de volgende voorbeelden:

FORMAT( dt”2020-12-15T12:30:59″, “mm/dd/yyyy”, “en-GB” )
FORMAT( dt”2020-12-15T12:30:59″, “dd mmmm yyyy”, “pt-BR” )
FORMAT( dt”2020-12-15T12:30:59″, “dd mmmm yyyy”, “ru-RU” )
FORMAT( 1234.567, “#,0.00”, “en-US” )
FORMAT( 1234.567, “#,0.00”, “de-DE” )

De output van deze functies is als volgt:

12/15/2020
15 dezembro 2020
15 декабря 2020
1,234.57
1.234,57

Kijk voor meer info in de Microsoft documentatie nl/dax/format-function-dax of check de website DAX.GUIDE

Wil je meer leren over best practice werkmethoden in Excel en Power BI? Schrijf je dan in voor één van de trainingen via deze website.