LuckyTemplates Power Query: päällekkäisten rivien poistaminen

Tässä blogissa näytän sinulle, kuinka voit poistaa päällekkäisiä rivejä LuckyTemplates Power Querysta ehtojen perusteella. Voit katsoa tämän opetusohjelman koko videon tämän blogin alaosasta.

Tämä esimerkki on peräisin kysymyksestä, joka esitettiin. Jäsenellä on tapahtumatietoja ja hän haluaa poistaa rivit, jotka kumoavat toisensa.

On olemassa kaksi erillistä tapahtumatyyppiä, yksi saapuville ja toinen lähteville. Hän on myös lisännyt liikemäärää. Ja sen perusteella hän muotoili kolme skenaariota.

Ensimmäinen skenaario perustuu kahteen riviin. Jos ne kumoavat toisensa, molemmat rivit tulee poistaa. Jos näin ei ole, molemmat rivit tulee säilyttää.

Skenaariot kaksi ja kolme perustuvat rivien yhdistelmään, joka osittain kumoaa toisensa. Nämä rivit tulisi poistaa ensin ensin ulos -periaatteella, jotta vain viimeisimmät saapuvat tapahtumat säilyvät.

Periaatteessa toistamme samaa logiikkaa yhä uudelleen ja uudelleen. Meidän pitäisi pystyä suunnittelemaan yksi lähestymistapa, joka sopii kaikkiin näihin skenaarioihin.

Aiomme lisätä sarakkeen absoluuttisilla määrillä ja ryhmitellä "lisää määrätasapainosarake" ja sisäkkäisen taulukon "kaikki rivit" -vaihtoehdon avulla. Muutamme myös sisäkkäiset taulukot, lajittelemme siirtotyypin ja kirjauspäivämäärän, luomme juoksevan summan ja säilytämme rivit vain ehdon perusteella.

Olen arvioinut tämän keskitasolle, koska suurin osa muutoksista, joita voimme tehdä täällä, on käyttöliittymän avulla. Tämän jälkeen siirrytään LuckyTemplates Power Queryyn.

Sisällysluettelo

• Absoluuttisia määriä sisältävän sarakkeen lisääminen Power Queryyn
• Ryhmittele tunnusindeksin ja absoluuttisen määrän mukaan
• Sisäkkäisten taulukoiden muuntaminen LuckyTemplates Power Queryssä
  • Liikkeen lajittelutyyppi ja kirjauspäivämäärä
  • Juoksevan kokonaissumman luominen
  • Rivien suodatus ehdon perusteella
• Johtopäätös

Absoluuttisia määriä sisältävän sarakkeen lisääminen Power Queryyn

Aloitamme lisäämällä sarakkeen absoluuttisilla määrillä. Valitse Määrä- sarake, siirry Lisää sarake -nauhasta tai -välilehdestä Tieteellinen ja napsauta sitten Absoluuttinen arvo .

Nimeän tämän sarakkeen uudelleen kaavapalkissa.

Ryhmittele tunnusindeksin ja absoluuttisen määrän mukaan

Voimme nyt ryhmitellä ID-indeksin ja absoluuttisen määrän mukaan. Valitse ID-indeksi , pidä sitten Shift- tai Control-näppäintä painettuna monivalintaa varten ja napsauta sitten Abs qty (absoluuttinen määrä).

Löydät ryhmittelyperusteen Muunna-välilehdeltä.

Löydät sen kuitenkin myös Koti-välilehdeltä.

Napsauta sitä ja nyt lisäämme uuden sarakkeen. Se ei ole "luku", mutta se tulee olemaan määrän "summa". Joten nimetään se uudelleen määräksi (määrä) Balance . Se tulee olemaan määräsarakkeen summa. Ja sitten lisäämme toisen koosteen, mutta se tulee olemaan erityinen, joten valitsemme Kaikki rivit . Annetaan tälle sarakkeelle myös nimi ( AllRows ). Paina sitten OK.

Jos napsautamme tyhjää tilaa taulukossa, näemme alla esikatselun sisäkkäisestä taulukosta. Näemme, että tämän sisäkkäisen taulukon määrät kumoavat toisensa ja määräsaldo on nolla.

Jos katsomme seuraavaa sisäkkäistä taulukkoa (eli napsauta sivulle seuraavan rivin tyhjässä tilassa), huomaamme, että määräsarakkeet eivät kumoa toisiaan ja määräsaldo on suurempi kuin nolla.

Lisätään mukautettu sarake, jotta voimme käyttää sitä sisäkkäisten taulukoiden muunnoksissamme myöhemmin. Valitse siis Lisää mukautettu sarake .

Nimetään se sarake uudelleen muotoonja lisäämme nollan (0) paikkamerkiksi. Napsauta sitten OK.

Sisäkkäisten taulukoiden muuntaminen LuckyTemplates Power Queryssä

Olemme nyt valmiita luomaan logiikan näiden sisäkkäisten taulukoiden muuntamiseksi. Mutta emme halua kirjoittaa niitä joukkoa, eikä meidän tarvitsekaan.

Voimme käyttää käyttöliittymää suurimman osan. Jos rakennamme näiden muunnosten logiikan erillisessä kyselyssä, monimutkaisin skenaario, joka meillä oli, oli joko kolmen tai neljän rivin skenaario.

Jos nyt napsautan viimeisen rivin oikeaa reunaa, huomaan, että tässä taulukossa on neljä riviä. Joten voimme käyttää tätä muutosten rakentamiseen.

Napsauta hiiren kakkospainikkeella tyhjän tilan reunaa ja valitse Lisää uutena kyselynä .

Näemme, että sisäkkäinen taulukko on nyt laajentunut ja lisätty uutena kyselynä, ja voimme käyttää tätä tarvitsemamme logiikan rakentamiseen.

Liikkeen lajittelutyyppi ja kirjauspäivämäärä

Ensimmäinen asia, joka meidän on tehtävä, on lajitella liikkeen tyypin mukaan . Valitsetaan laskeva . Tällä tavalla lähtevät tapahtumat ovat aina kärjessä.

Seuraavaksi lajittelemme lähetyspäivämäärän nousevaan järjestykseen ja varmistamme, että jos jätämme rivit pois, se tehdään aina FIFO-periaatteen mukaisesti (first in first out).

Juoksevan kokonaissumman luominen

Tunnistaakseni poistettavat rivit lisään poistettavan kokonaismäärän, ja voimme käyttää List.FirstN-tiedostoa kyseiseen tehtävään. Tämä toiminto luo listan toisen listan perusteella, jossa suosituimmat kohteet säilytetään tietyn numeron tai kriteerin perusteella.

Palataan kyselyymme. Tarvitsemme kaksi asiaa List.FirstN:lle. Ensinnäkin tarvitsemme sen luettelon, ja tämä luettelo on Määrä- sarakkeemme. Jos napsautan hiiren oikealla painikkeella Määrä-sarakkeen otsikkoa, saamme vaihtoehdon Add as NewQuery .

Ja tässä näemme koodin, joka meidän on luotava luettelo. Sovelletussa vaiheessa voit nähdä, että se osoittaa viimeiseen vaiheeseen ja tunnistaa sitten sarakkeen kyseisissä suluissa.

Toinen osa, jonka tarvitsemme List.FirstN:lle, on numero, joka määrittää, kuinka monta numeroa luettelosta säilytetään. Voimme käyttää indeksiä tähän, joten aion lisätä indeksisarakkeen yhdestä (1) tälle riville.

Joten numerolla 1 se säilyttää numeron Määrä-sarakkeen ensimmäisellä rivillä ja niin edelleen. Ja voimme nyt tiivistää sen.

Lisätään mukautettu sarake ja kutsutaan sitä Running Total . Käytämme tässä List.FirstN:ää, ja voimme nähdä, että ensimmäinen parametri oli tuo lista, Määrä-sarake. Meidän on osoitettava viimeiseen vaiheeseen, ja viimeinen vaihe on lisätty indeksi .

Tunnistamme haluamamme sarakkeen, ja se on Määrä-sarakkeemme. Laskumme on Indeksi-sarakkeessa.

Nyt se palauttaa luettelon. Jos napsautamme valkoisen tilan sivua, näemme luettelon sisällön. Ensimmäisellä tietueella se säilytti vain listan ylimmän rivin. Toisella ennätyksellä se piti kaksi ylintä riviä.

Nyt meidän tarvitsee vain summata nämä määrät ja voimme tehdä sen käyttämällä List.Sum-ohjelmaa . Joten aion lisätä sen kaavapalkkiin. Ja aion myös laittaa Typen .

Rivien suodatus ehdon perusteella

Tämän juoksevan summan avulla voimme tunnistaa rivit, jotka haluamme säilyttää. Haluamme säilyttää vain rivit, jotka ovat suurempia kuin nolla, joten lisätään suodatusehto.

Voimme nyt poistaa apurarakkeet. Valitse Indeksi ja juoksusumma ja napsauta sitten Poista sarakkeet.

Kehittyneessä muokkausohjelmassa voimme nyt kopioida luomamme koodin. Avaan , ja näemme, että tämä on jaettu vaiheemme. Joten voimme valita ja kopioida kaiken alla olevan.

Palataan nyt alkuperäiseen kyselyymme. Avaa uudelleen Advanced Editor. Ja paikkamerkin sijaan siirrytään uudelle riville. Aion käyttää " let " -lauseketta, koska se voi kaapata muuttujien välilaskennan arvoja. Joten "anna" ja siirry sitten uudelle riville ja liitä muunnokseni. Ilmoitan myös tyypin .

Yllä oleva korostettu koodi osoittaa laajennettuun sisäkkäiseen taulukkoomme, joka on sijoitettu Kaikki rivit -sarakkeeseen. Osoitan siis tämän korvaamalla tämän korostetun koodin AllRowsilla .

Joten muunnossarakkeessamme on nyt sisäkkäisiä taulukoita. Rakennamme kyselymme viimeisen rivin perusteella, eikö niin? Ja siinä oli neljä riviä, kun aloitimme, ja vain kaksi. Meillä on myös ne määrät tasapainossa nollassa, eikö niin? Ja nyt siinä on tyhjä pöytä.

Voimme poistaa ne suodattamalla Määräsaldo-sarakkeessa. Emme halua määrätasapainon olevan nolla.

Ja sitten aion valita muunnossarakkeeni ja poistaa muut sarakkeet. Voin nyt laajentaa muunnosten sisäkkäisiä taulukoita. Älä käytä alkuperäistä sarakkeen nimeä etuliitteenä ja paina OK.

Valitse Muunna-välilehdeltä Tietotyyppi ja kaikki on valmis. Ja näin puhdistat tapahtumatiedot.

Johtopäätös

Tässä opetusohjelmassa näytin sinulle, kuinka voit puhdistaa tapahtumatiedot ja erityisesti poistaa päällekkäiset rivit. Tämä on loistava tekniikka, jota voit käyttää LuckyTemplates Power Queryn käytössä.

Toivottavasti pidit tästä. Katso koko video-opetusohjelma alta saadaksesi lisätietoja. Katso myös alla olevista linkeistä lisää LuckyTemplates Power Query Editorin liittyvää sisältöä.

Kippis!

Melissa