Opprette verdier for hver dato ved å bruke Power Query Formula

I denne opplæringen lærer du hvordan du lager verdier for hver dag ved å bruke formelen i LuckyTemplates.

Å lage verdier for datoer er en viktig teknikk å lære i LuckyTemplates. Den kan gi deg dataene du vil se gjennom dagene i modellen din. Denne teknikken dekker også gapene mellom kolonnene.

Dette er et flott verktøy å bruke når datotabellene dine mangler informasjon.

Her er en trinn-for-trinn-guide for hvordan du gjør det: Du kan se hele videoen av denne opplæringen nederst på denne bloggen.

Innholdsfortegnelse

• Angi tilleggsverdier
• Opprett filterbetingelsen for ID-ene
• Legg til filterbetingelsen for datoene
• Sjekk og sorter datokolonnen
• Bruk Table.FirstN-funksjonen
• Få dagsforskjellen mellom 2 datoverdier
• Bestem et vekttrinn
• Fyll tomrommet
• Få vektprojeksjonen av datoer
• Konklusjon

Angi tilleggsverdier

For datasett som inneholder IDer, datoer og vekter, må du fylle gapet mellom disse målingene. Dette kan gjøres ved å opprette en datoperiode for å spre forskjellen over dagene.

For å gjøre det, gå til Power Query for å se tabellen.

Du må laste inn en kopi av dataene dine i minnet. For at det skal skje, må du bruke Table.Buffer- målet.

Hvis du klikker TableBuffer, vil du se hva tiltaket gjør.

Gå deretter tilbake til datasettet og klikk på fx i formellinjen. Du må legge til Table.Buffer i målet.

Gi deretter nytt navn til dette trinnet til BufferedTable .

Deretter legger du til en egendefinert kolonne.

Opprett filterbetingelsen for ID-ene

I denne Power Query-formelen endrer du det refererte trinnet fra BufferedTable til RenameColumns fordi BufferedTable bør sjekkes først.

For å sjekke BufferedTable, bruk Table.SelectRows som returnerer en tabell som samsvarer med den valgte betingelsen.

Så for hvert uttrykk i formelen bruker du Table.SelectRows og deretter BufferedTable.

Som en filterbetingelse, bruk en egendefinert sluttfunksjon og skriv inn BT for Bufret Table . Sjekk ID-kolonnen hvis den er lik IDen fra gjeldende post. Deretter får du tilgang til gjeldende post ved å bruke understreken som tilgangsoperatør, og referer deretter til den samme ID-kolonnen.

Dette vil gi deg en filtrert tabell som bare inneholder ID-ene som samsvarer med gjeldende post. 

Legg til filterbetingelsen for datoene

Du må filtrere datokolonnen fordi du bare trenger datoene som er lik gjeldende post.

Bruk disse funksjonene for å sjekke BT-datokolonnen:

Denne kolonnen må være større enn eller lik datoen fra gjeldende post.

Med dette vil tabellen da inneholde to rader i stedet for tre:

Sjekk og sorter datokolonnen

Du må sjekke om sorteringen av datokolonnen er riktig ved å bruke Table.Sort . Funksjonen Table.Sort sorterer en tabell på en eller flere kolonner avhengig av hvilke kriterier du ønsker.

Syntaksen i dette eksemplet er ganske komplisert, så du må lage en ned-drill på et tabellemne.

Du må høyreklikke på en tabell og legge den til som en ny spørring.

Hvis du klikker på det, får du et tilpasset søk som inneholder den tabellen.

Klikk deretter på datokolonnen og velg Sorter stigende.

Kopier hele formelen:

Gå tilbake til hovedspørsmålet og lim det inn i formellinjen:

Du må fjerne referansen til Table.Sort fordi tabellen som må passere parameteren skal være resultatet fra Table.SelectRows.

Når du er ferdig, legger du tilbake sorteringsbetingelsene. Etter å ha tatt i bruk endringene, er tabellen nå sortert riktig etter dato.

Bruk Table.FirstN-funksjonen

For dette trinnet trenger du bare 2 poster i stedet for 3.

For å fikse dette, gå tilbake til det tilpassede søket. Klikk på Behold rader og deretter Behold øverste rader.

Skriv inn 2 i det tomme for antall rader.

Du vil legge merke til at Table.FirstN-funksjonen vises i formellinjen. Table.FirstN returnerer den første raden(e) i tabellen avhengig av verdien til countOrCondition .

Sett inn denne funksjonen, sammen med antall rader du trenger i hovedspørringen. Ta med funksjonstypetabellen på slutten .

Hvert tabellobjekt i settet vil nå ha enten 1 eller 2 rader.

Få dagsforskjellen mellom 2 datoverdier

Neste trinn er å få antall dager mellom datoverdiene og trekke ut datoen som var på den andre posten.

Start med å legge til en egendefinert kolonne. Name it Days . Følg deretter denne Power Query-formelen:

Syntaksen sørger for at det er 2 poster i en tabell. Hvis det bare er 1, er dagsforskjellen 0 fordi den ikke har noen neste rekord. Hvis det er 2, kan du få forskjellen fra datoverdiene.

Deretter legger du til typenummer i formelen slik.

Som du kan se, får du en forskjell på 385 dager fra 14. september 2017 til 4. oktober 2018.

Bestem et vekttrinn

Deretter må du beregne hva vekten for hver dag skal være.

Du må legge til en egendefinert kolonne igjen, gi den navnet WeightStep og kopiere denne Power Query-formelen:

Syntaksen sjekker om tabellen har mer enn 1 rad, trekker vekten fra gjeldende post og deler den forskjellen på antall dager.

Igjen, legg til typenummer i formelen slik at du kan se vekten for hver dag.

Fyll tomrommet

Nå som du har antall dager, kan du opprette en liste over datoer for å fylle gapet i datasettet.

Først legger du til en annen egendefinert kolonne og kaller den DateKey . Følg deretter denne Power Query-formelen:

Formelen sjekker om det er en datoforskjell. Hvis det er det, oppretter den en liste over datoer mellom startdatoen som er fra gjeldende post, og utvider den med antall dager. Men hvis det ikke er noen forskjeller, returnerer den datoen.

Dette er resultatet:

Tabellemnene som ikke hadde noen forskjeller fikk datoen tilbake; tabellene som gjorde det, fikk en liste over datoer.

Hvis du utvider DateKey til nye rader, kan du se postene for alle dagene.

Få vektprojeksjonen av datoer

Etter å ha opprettet en datoliste, må du få en vektprojeksjon for datoene.

Først legger du til en egendefinert kolonne, og gir den navnet WeightProjection . Skriv deretter inn denne Power Query-formelen:

Syntaksen henter vektverdien, legger den til DateKey-verdien, trekker den fra datoverdien og multipliserer den med WeightStep.

Igjen, legg til typenummer på slutten av formelen.

Du har nå vektprojeksjonen for hver dato.

Fjern hjelpekolonnene: Custom, Days og WeightStep ved å bruke formelen nedenfor, og endre formatet på DateKey-kolonnen til Dato.

Konklusjon

I denne opplæringen lærte du hvordan du fyller hullene i datasettet ditt ved å lage verdier i hver datoliste.

Disse datoverdiene hjelper deg med å få verdifull innsikt fra modellen din. Hvis tabellene mangler informasjon, kan du bruke denne teknikken for å gjøre dem mer omfattende.

Det kan være viktige data i tabellen din som du utelater, så maksimer alltid hver rad og kolonne.

Beste ønsker,

Melissa