Du er kanskje godt kjent med parameterspørringer og bruken av dem i SQL eller Microsoft Query. Power Query-parametere har imidlertid viktige forskjeller:
- Parametere kan brukes i alle spørringstrinn. I tillegg til å fungere som et datafilter, kan parametere brukes til å angi ting som en filbane eller et servernavn.
- Parametere ber ikke om inndata. I stedet kan du raskt endre verdiene ved hjelp av Power Query. Du kan også lagre og hente verdiene fra celler i Excel.
- Parametere lagres i en enkel parameterspørring, men er atskilt fra dataspørringene de brukes i. Når den er opprettet, kan du legge til en parameter i spørringer etter behov.
Vær oppmerksom på Hvis du vil bruke den andre måten til å opprette parameterspørringer, kan du se Opprette en parameterspørring i Microsoft Query.
Opprette en parameter
Du kan bruke en parameter til automatisk å endre en verdi i en spørring, og unngå å redigere spørringen hver gang du vil endre verdien. Du endrer bare parameterverdien. Når du har opprettet en parameter, lagres den i en spesiell parameterspørring som du enkelt kan endre direkte fra Excel.
Velg data>Hent data>Andre kilder>Start Power Query-redigering.
Velg Hjem>Behandle parametere > Nye parametere i Power Query-redigering.
Velg Ny i dialogboksen Behandle parameter.
Angi følgende etter behov:
Navn Dette skal gjenspeile parameterens funksjon, men hold det så kort som mulig. Beskrivelse Denne kan inneholde alle detaljer som vil hjelpe folk med å bruke parameteren riktig. Obligatorisk Gjør ett av følgende:
Hvilken som helst verdi Du kan angi en hvilken som helst verdi for en hvilken som helst datatype i parameterspørringen.
Liste over verdier Du kan begrense verdiene til en bestemt liste ved å skrive dem inn i det lille rutenettet. Du må også velge en standardverdi og en gjeldende verdi nedenfor.
Spørring Velg en listespørring, som ligner på en strukturert listekolonne atskilt med komma og omsluttet av klammeparenteser.
Et Problemstatus-felt kan for eksempel ha tre verdier: {"Ny", "Pågår", "Lukket"}. Du må opprette listespørringen på forhånd ved å åpne avansert redigering (velg Hjemavansert>redigering), fjerne kodemalen, angi listen over verdier i spørringslisteformatet og deretter velge Ferdig.
Når du er ferdig med å opprette parameteren, vises listespørringen i parameterverdiene.Type Dette angir datatypen for parameteren. Foreslåtte verdier Hvis du ønsker, kan du legge til en liste over verdier eller angi en spørring for å gi forslag til inndata. Standardverdi Dette vises bare hvis Foreslåtte verdier er satt til Liste over verdier og angir hvilket listeelement som er standard. I så fall må du velge en standard. Gjeldende verdi Hvis parameteren er tom, kan det hende at spørringen ikke returnerer noen resultater, avhengig av hvor du bruker parameteren. Hvis Obligatorisk er valgt, kan ikke gjeldende verdi være tom. Velg OK for å opprette parameteren.
Bruke en parameter til å endre en datakilde
Her er en måte du kan administrere endringer i datakildeplasseringer og bidra til å forhindre oppdateringsfeil. Hvis vi for eksempel antar et lignende skjema og en lignende datakilde, kan du opprette en parameter for enkelt å endre en datakilde og bidra til å forhindre dataoppdateringsfeil. Noen ganger endres serveren, databasen, mappen, filnavnet eller plasseringen. Kanskje en databasebehandler av og til bytter ut en server, en månedlig slipp av CSV-filer går til en annen mappe, eller du trenger å bytte enkelt mellom et utviklings-/test-/produksjonsmiljø.
Trinn 1: Opprett en parameterspørring
I eksemplet nedenfor har du flere CSV-filer som du importerer ved hjelp av importmappen (Velg data>Hent data>fra Files>From Folder) fra mappen C:\DataFilesCSV1. Men noen ganger brukes en annen mappe som en plassering for å utelate filene, C:\DataFilesCSV2. Du kan bruke en parameter i en spørring som en erstatningsverdi for den andre mappen.
Velg Hjem>Behandle parametere>Ny parameter.
Skriv inn følgende informasjon i dialogboksen Behandle parameter :
Navn CSVFileDrop Beskrivelse Alternativ plassering for filslipp Obligatorisk Ja Type Tekst Foreslåtte verdier Hvilken som helst verdi Gjeldende verdi C:\DataFilesCSV1 Velg OK.
Trinn 2: Legge til parameteren i dataspørringen
- Hvis du vil angi mappenavnet som en parameter, velger du Kilde under Spørringstrinn i Spørringsinnstillinger og deretter Rediger innstillinger.
- Kontroller at alternativet Filbane er satt til Parameter, og velg deretter parameteren du nettopp opprettet, fra rullegardinlisten.
- Velg OK.
Trinn 3: Oppdater parameterverdien
Mappeplasseringen ble nettopp endret, så nå kan du ganske enkelt oppdatere parameterspørringen.
- Velg Datatilkoblinger>& Spørringer Spørringer-fanen>, høyreklikk på parameterspørringen, og velg deretter Rediger.
- Skriv inn den nye plasseringen i boksen Gjeldende verdi , for eksempel C:\DataFilesCSV2.
- Velg Hjem>Lukk & Last inn.
- Hvis du vil bekrefte resultatene, legger du til nye data i datakilden, og deretter oppdaterer du dataspørringen med den oppdaterte parameteren (Velg data>oppdater alt).
Bruke en parameter til å filtrere data
Noen ganger ønsker du en enkel måte å endre filteret for en spørring på for å få forskjellige resultater uten å redigere spørringen eller lage litt forskjellige kopier av samme spørring. I dette eksemplet endrer vi en dato for å endre et datafilter på en praktisk måte.
Hvis du vil åpne en spørring, finner du en som tidligere ble lastet inn fra Power Query-redigering, velger en celle i dataene og velger deretter Spørringsredigering>. Hvis du vil ha mer informasjon, kan du se Opprette, laste inn eller redigere en spørring i Excel.
Velg filterpilen i en kolonneoverskrift for å filtrere dataene, og velg deretter en filterkommando, for eksempel Dato/klokkeslett-filtrering>etter. Dialogboksen Filtrer rader vises.
Velg knappen til venstre for verdiboksen , og gjør deretter ett av følgende:
- Hvis du vil bruke en eksisterende parameter, velger du Parameter, og deretter velger du parameteren du vil bruke fra listen som vises til høyre.
- Hvis du vil bruke en ny parameter, velger du Ny parameter, og deretter oppretter du en parameter.
Skriv inn den nye datoen i boksen Gjeldende verdi , og velg deretter Hjem>Lukk & Last inn.
Hvis du vil bekrefte resultatene, legger du til nye data i datakilden, og deretter oppdaterer du dataspørringen med den oppdaterte parameteren (Velg data>oppdater alt). Endre for eksempel filterverdien til en annen dato for å se nye resultater.
Skriv inn den nye datoen i boksen Gjeldende verdi .
Velg Hjem>Lukk & Last inn.
Hvis du vil bekrefte resultatene, legger du til nye data i datakilden, og deretter oppdaterer du dataspørringen med den oppdaterte parameteren (Velg data>oppdater alt).
Bruke en celleverdi til å filtrere data
I dette eksemplet leses verdien i spørringsparameteren fra en celle i arbeidsboken. Du trenger ikke å endre parameterspørringen, du bare oppdaterer celleverdien. Du vil for eksempel filtrere en kolonne etter første bokstav, men enkelt endre verdien til en hvilken som helst bokstav fra A til Å.
Opprett en Excel-tabell i regnearket i en arbeidsbok der spørringen du vil filtrere er lastet inn, en Excel-tabell med to celler: en overskrift og en verdi.
Mitt filter G Merk en celle i Excel-tabellen, og velg deretter Hent>data>fra tabell/område. Power Query-redigering vises.
I Navn-boksen i feltet Spørringsinnstillinger til høyre endrer du navnet på spørringen til å være mer meningsfylt, for eksempel FilterCellValue.
Hvis du vil overføre verdien i tabellen, og ikke selve tabellen, høyreklikker du verdien i forhåndsvisning av data og velger Drill ned.
Legg merke til at formelen endres til= #"Changed Type"{0}[MyFilter]
Når du bruker Excel-tabellen som et filter i trinn 10, refererer Power Query til tabellverdien som filterbetingelsen. En direkte referanse til Excel-tabellen vil forårsake feil.Velg Hjem>Lukk & Last inn>Lukk & Last inn til. Du har nå en spørringsparameter kalt «FilterCellValue» som du bruker i trinn 12.
Velg Bare lag tilkobling i dialogboksen Importer data, og velg deretter OK.
Åpne spørringen du vil filtrere med verdien i FilterCellValue-tabellen, en som tidligere er lastet inn fra Power Query-redigering, ved å merke en celle i dataene og deretter velge Spørringsredigering>. Hvis du vil ha mer informasjon, kan du se Opprette, laste inn eller redigere en spørring i Excel.
Velg filterpilen i en kolonneoverskrift for å filtrere dataene, og velg deretter en filterkommando, for eksempel Tekstfiltre>begynner med. Dialogboksen Filtrer rader vises.
Skriv inn en verdi i Verdi-boksen, for eksempel «G», og velg deretter OK. I dette tilfellet er verdien en midlertidig plassholder for verdien i FilterCellValue-tabellen, som du angir i neste trinn.
Velg pilen på høyre side av formellinjen for å vise hele formelen. Her er et eksempel på en filterbetingelse i en formel:
= Table.SelectRows(#"Changed Type", each Text.StartsWith([Name], "G"))
Velg verdien for filteret. Velg G i formelen.
Bruk M IntelliSense til å skrive inn de første bokstavene i FilterCellValue-tabellen du opprettet, og deretter velger du den fra listen som vises.
Velg Hjem>:Lukk>Lukk & Last inn.
Resultat
Spørringen bruker nå verdien i Excel-tabellen som du opprettet, til å filtrere spørringsresultatene. Hvis du vil bruke en ny verdi, redigerer du celleinnholdet i den opprinnelige Excel-tabellen i trinn 1, endrer G til V og oppdaterer spørringen.
Kontrollere bruken av parameterspørringer
Du kan bestemme om parameterspørringer er tillatt eller ikke.
- Velg Filalternativer>og Innstillinger>Spørringsalternativer> i Power Query-redigeringPower Query-redigering.
- I ruten til venstre, under GLOBAL, velger du Power Query-redigering.
- Merk av for eller fjern merket for Tillat alltid parametrisering i dialogbokser for datakilde og transformering i ruten til høyre, under Parametere.