{"id":6759,"date":"2025-01-13T05:06:27","date_gmt":"2025-01-13T05:06:27","guid":{"rendered":"https:\/\/howtogeek.blog\/lv\/?p=6759"},"modified":"2025-01-13T05:06:27","modified_gmt":"2025-01-13T05:06:27","slug":"cleaning-and-importing-data-with-power-query-in-excel-a-stepbystep-guide","status":"publish","type":"post","link":"https:\/\/howtogeek.blog\/lv\/cleaning-and-importing-data-with-power-query-in-excel-a-stepbystep-guide\/","title":{"rendered":"Datu t\u012br\u012b\u0161ana un import\u0113\u0161ana, izmantojot Power Query programm\u0101 Excel: soli pa solim"},"content":{"rendered":"<h3>\u0100tr\u0101s saites<\/h3>\n<p>Kop\u0161 debijas programm\u0101 Excel Power Query ir main\u012bjis veidu, k\u0101 profesion\u0101\u013ci apstr\u0101d\u0101 datu p\u0101rvald\u012bbu. S\u0101kotn\u0113ji 2010.\u00a0gad\u0101 ieviests k\u0101 pievienojumprogramma, Power Query kop\u0161 2016.\u00a0gada ir k\u013cuvis par neaizst\u0101jamu ieb\u016bv\u0113to l\u012bdzekli programm\u0101 Excel. Vai t\u0101 ir galven\u0101 funkcija? Datu t\u012br\u012b\u0161anas, import\u0113\u0161anas un p\u0101rk\u0101rto\u0161anas procesa racionaliz\u0113\u0161ana, lai ietaup\u012btu ne tikai laiku, bet ar\u012b palielin\u0101tu neapmierin\u0101t\u012bbu. Koncentr\u0113joties uz datu efekt\u012bvu sagatavo\u0161anu, Power Query \u013cauj koncentr\u0113ties uz anal\u012bzi un ieskatiem.<\/p>\n<h2 id=\"what-is-power-query\">Kas ir Power Query?<\/h2>\n<p>Power Query b\u016bt\u012bb\u0101 ir r\u012bks programm\u0101 Excel, kas paredz\u0113ts datu p\u0101rveido\u0161anai. Tas pied\u0101v\u0101 vienk\u0101r\u0161u, bet jaud\u012bgu veidu, k\u0101 izg\u016bt datus no da\u017e\u0101diem avotiem, not\u012br\u012bt tos un sagatavot anal\u012bzei. Power Query atrodas cilnes Dati sada\u013c\u0101 \u201cDatu ieg\u016b\u0161ana un p\u0101rveido\u0161ana\u201d, nodro\u0161inot vieglu piek\u013cuvi lietot\u0101jiem visos prasmju l\u012bme\u0146os.<\/p>\n<figure class=\"wp-block-image\"><img alt=\"Power Query r\u012bku atra\u0161an\u0101s vieta programm\u0101 Excel ciln\u0113 Dati\" class=\"wp-image\" decoding=\"async\" height=\"243\" loading=\"lazy\" src=\"https:\/\/cdn.howtogeek.blog\/wp-content\/uploads\/2025\/01\/the-data-tab-in-excel-with-the-get-and-transform-data-group-highlighted.webp\" title=\"Power Query r\u012bku atra\u0161an\u0101s vieta programm\u0101 Excel ciln\u0113 Dati\" width=\"750\"\/><\/figure>\n<p>Piespr\u0101dz\u0113jieties, kad m\u0113s iedzi\u013cin\u0101mies Power Query praktiskajos lietojumos, nodro\u0161inot j\u016bs ar zin\u0101\u0161an\u0101m, k\u0101 smalki t\u012br\u012bt, import\u0113t un k\u0101rtot savus datus.<\/p>\n<h2 id=\"cleaning-excel-data-using-power-query\">Excel datu t\u012br\u012b\u0161ana, izmantojot Power Query<\/h2>\n<p>Viena no man\u0101m Power Query lietojumprogramm\u0101m ir datu t\u012br\u012b\u0161ana. Iedom\u0101jieties, ka sav\u0101 Excel lap\u0101 esat iel\u012bm\u0113jis neparastu e-pasta adre\u0161u sarakstu no Outlook e-pasta lauka CC. Tava misija? Lai p\u0101rv\u0113rstu \u0161os net\u012bros datus par sak\u0101rtotu tabulu, kur\u0101 ir v\u0101rdi, uzv\u0101rdi un e-pasta adreses. Lai gan Excel funkcijas var to paveikt, Power Query nodro\u0161ina nevainojamu pieredzi, kas prasa minim\u0101lu piep\u016bli.<\/p>\n<figure class=\"wp-block-image\"><img alt=\"Programm\u0101 Excel ar semikolu atdal\u012btu e-pasta adre\u0161u saraksts\" class=\"wp-image\" decoding=\"async\" height=\"147\" loading=\"lazy\" src=\"https:\/\/cdn.howtogeek.blog\/wp-content\/uploads\/2025\/01\/a-list-of-email-addresses-in-cell-a1-of-an-excel-spreadsheet-each-separated-by-a-semicolon.webp\" title=\"Programm\u0101 Excel ar semikolu atdal\u012btu e-pasta adre\u0161u saraksts\" width=\"750\"\/><\/figure>\n<p>Lai s\u0101ktu, veiciet t\u0101l\u0101k nor\u0101d\u012bt\u0101s darb\u012bbas.<\/p>\n<ul>\n<li><strong>Atveriet Power Query:<\/strong> noklik\u0161\u0137iniet uz cilnes \u201cDati\u201d, p\u0113c tam atlasiet \u201cFrom Table\/Range\u201d. \u0160aj\u0101 log\u0101 tiek pied\u0101v\u0101ts izveidot tabulu no atlas\u012btajiem datiem.<\/li>\n<li><strong>Atlasiet savus datus:<\/strong> apstipriniet, ka ir atlas\u012bts pareizais diapazons, un, ja j\u016bsu datiem nav galvenes, pirms noklik\u0161\u0137in\u0101\u0161anas uz Labi atst\u0101jiet galvenes opciju neatz\u012bm\u0113tu.<\/li>\n<\/ul>\n<p>Noklik\u0161\u0137inot uz \u201cOK\u201d, tiek atv\u0113rts Power Query redaktors, kas nosaka j\u016bsu datu p\u0101rveido\u0161anas stadiju.<\/p>\n<p>Tavs n\u0101kamais uzdevums? Sadaliet e-pasta adreses atsevi\u0161\u0137\u0101s rind\u0101s:<\/p>\n<ul>\n<li><strong>Sadal\u012bt kolonnu:<\/strong> ar peles labo pogu noklik\u0161\u0137iniet uz kolonnas galvenes, virziet kursoru virs \u201cSadal\u012bt kolonnu\u201d un atlasiet \u201cP\u0113c norobe\u017eot\u0101ja\u201d. K\u0101 atdal\u012bt\u0101ju izv\u0113lieties \u201cSemikols\u201d un dialoglodzi\u0146\u0101 Papildu opcijas atlasiet \u201cRindas\u201d.<\/li>\n<\/ul>\n<p>P\u0113c apstiprin\u0101\u0161anas katra e-pasta adrese b\u016bs sak\u0101rtota atsevi\u0161\u0137\u0101s rind\u0101s.<\/p>\n<figure class=\"wp-block-image\"><img alt=\"E-pasta adreses, kas Power Query redaktor\u0101 ir sadal\u012btas vair\u0101k\u0101s rind\u0101s\" class=\"wp-image\" decoding=\"async\" height=\"254\" loading=\"lazy\" src=\"https:\/\/cdn.howtogeek.blog\/wp-content\/uploads\/2025\/01\/a-list-of-email-addresses-in-excel-s-power-query-editor.webp\" title=\"E-pasta adreses, kas Power Query redaktor\u0101 ir sadal\u012btas vair\u0101k\u0101s rind\u0101s\" width=\"750\"\/><\/figure>\n<p>Tagad ir pien\u0101cis laiks izvilkt v\u0101rdu un uzv\u0101rdu. \u0160im nol\u016bkam izmantojiet funkciju \u201cPievienot kolonnu no piem\u0113riem\u201d:<\/p>\n<ul>\n<li><strong>Izvilkt nosaukumus:<\/strong> veiciet dubultklik\u0161\u0137i uz kolonnas galvenes un ievadiet v\u0101rdu vai nosaukumu, lai kop\u0113tu modeli. Power Query autom\u0101tiski ieteiks atliku\u0161os ierakstus!<\/li>\n<li><strong>P\u0101rd\u0113v\u0113t kolonnas:<\/strong> noteikti p\u0101rd\u0113v\u0113jiet kolonnas atbilsto\u0161i (piem\u0113ram, \u201cV\u0101rds\u201d, \u201cUzv\u0101rds\u201d, \u201cE-pasta adrese\u201d).<\/li>\n<li><strong>P\u0113d\u0113jie piesk\u0101rieni:<\/strong> piel\u0101gojiet kolonnu sec\u012bbu, velkot t\u0101s vajadz\u012bgaj\u0101s poz\u012bcij\u0101s.<\/li>\n<\/ul>\n<p>T\u0101 k\u0101 j\u016bsu dati izskat\u0101s k\u0101rt\u012bgi un sak\u0101rtoti, neaizmirstiet savam vaic\u0101jumam pie\u0161\u0137irt j\u0113gpilnu nosaukumu, pirms aizverat un iel\u0101d\u0113jat datus atpaka\u013c darbgr\u0101mat\u0101.<\/p>\n<figure class=\"wp-block-image\"><img alt=\"Vaic\u0101juma nosaukuma pie\u0161\u0137ir\u0161ana Power Query redaktor\u0101\" class=\"wp-image\" decoding=\"async\" height=\"218\" loading=\"lazy\" src=\"https:\/\/cdn.howtogeek.blog\/wp-content\/uploads\/2025\/01\/a-table-in-excel-s-power-query-editor-being-given-the-name-heroicemails.webp\" title=\"Vaic\u0101juma nosaukuma pie\u0161\u0137ir\u0161ana Power Query redaktor\u0101\" width=\"750\"\/><\/figure>\n<h2 id=\"importing-and-reorganizing-data-with-power-query\">Datu import\u0113\u0161ana un p\u0101rk\u0101rto\u0161ana, izmantojot Power Query<\/h2>\n<p>Power Query ir izcila, kad runa ir par datu import\u0113\u0161anu no da\u017e\u0101diem avotiem, kas nav izkl\u0101jlapas, piem\u0113ram, PDF vai t\u012bmek\u013ca vietnes. \u0160aj\u0101 n\u0101kamaj\u0101 sada\u013c\u0101 ir par\u0101d\u012bts, k\u0101 efekt\u012bvi ieg\u016bt un p\u0101rk\u0101rtot konkr\u0113tus datus no eso\u0161as Excel darbgr\u0101matas.<\/p>\n<p>Vispirms s\u0101ciet, v\u0113lreiz p\u0101rejot uz cilni \u201cDati\u201d:<\/p>\n<ul>\n<li><strong>S\u0101ciet import\u0113\u0161anu:<\/strong> noklik\u0161\u0137iniet uz \u201cIeg\u016bt datus\u201d&gt; \u201cNo faila\u201d&gt; \u201cNo Excel darbgr\u0101matas\u201d.<\/li>\n<li><strong>Atlasiet savu failu:<\/strong> atrodiet failu, no kura import\u0113t datus, un noklik\u0161\u0137iniet uz \u201cImport\u0113t\u201d, p\u0113c tam noklik\u0161\u0137iniet uz \u201cTransform Data\u201d, lai piek\u013c\u016btu Power Query redaktoram.<\/li>\n<\/ul>\n<figure class=\"wp-block-image\"><img alt=\"Navigatora logs, kur\u0101 par\u0101d\u012bti import\u0113\u0161anai pieejamie dati\" class=\"wp-image\" decoding=\"async\" height=\"700\" loading=\"lazy\" src=\"https:\/\/cdn.howtogeek.blog\/wp-content\/uploads\/2025\/01\/excel-s-navigator-window-that-shows-the-data-it-will-import-from-another-worksheet-and-the-transform-data-button-is-selected.webp\" title=\"Navigatora logs, kur\u0101 par\u0101d\u012bti import\u0113\u0161anai pieejamie dati\" width=\"880\"\/><\/figure>\n<p>Power Query redaktor\u0101:<\/p>\n<ul>\n<li><strong>P\u0101rvietot rindu uz galven\u0113m:<\/strong> noklik\u0161\u0137iniet uz \u201cIzmantot pirmo rindu k\u0101 galvenes\u201d, lai uzreiz nor\u0101d\u012btu pirmo rindu k\u0101 sleju galvenes.<\/li>\n<li><strong>Atcelt datus:<\/strong> atlasiet kolonnas, kas att\u0113lo j\u016bsu unik\u0101los main\u012bgos, un ar peles labo pogu noklik\u0161\u0137iniet, lai atlas\u012btu \u201cAtcelt citas kolonnas\u201d. T\u0101d\u0113j\u0101di tiek p\u0101rveidots j\u016bsu izk\u0101rtojums, par\u0101dot katru unik\u0101lo v\u0113rt\u012bbu sav\u0101 rind\u0101.<\/li>\n<\/ul>\n<figure class=\"wp-block-image\"><img alt=\"Neapgriezti dati, kas par\u0101da sak\u0101rtotu izk\u0101rtojumu\" class=\"wp-image\" decoding=\"async\" height=\"311\" loading=\"lazy\" src=\"https:\/\/cdn.howtogeek.blog\/wp-content\/uploads\/2025\/01\/a-table-in-power-query-editor-in-excel-containing-five-columns-that-have-been-transformed-using-the-tool-s-mechanisms.webp\" title=\"Neapgriezti dati, kas par\u0101da sak\u0101rtotu izk\u0101rtojumu\" width=\"749\"\/><\/figure>\n<p>Pirms p\u0101rk\u0101rtot\u0101s tabulas iel\u0101d\u0113\u0161anas atpaka\u013c darbgr\u0101mat\u0101, neaizmirstiet prec\u012bzi noregul\u0113t kolonnu nosaukumus un defin\u0113t pareizos datu tipus, lai saglab\u0101tu datu integrit\u0101ti.<\/p>\n<figure class=\"wp-block-image\"><img alt=\"M\u0113ne\u0161a kolonnas datu veida iestat\u012b\u0161ana Power Query redaktor\u0101\" class=\"wp-image\" decoding=\"async\" height=\"413\" loading=\"lazy\" src=\"https:\/\/cdn.howtogeek.blog\/wp-content\/uploads\/2025\/01\/a-table-in-excel-s-power-query-editor-with-a-column-being-renamed-month-and-the-data-type-symbol-representing-a-calendar.webp\" title=\"M\u0113ne\u0161a kolonnas datu veida iestat\u012b\u0161ana Power Query redaktor\u0101\" width=\"750\"\/><\/figure>\n<p>Pabeidzot transform\u0101cijas, noklik\u0161\u0137iniet uz \u201cAizv\u0113rt un iel\u0101d\u0113t\u201d&gt; \u201cAizv\u0113rt un iel\u0101d\u0113t\u201d, lai noteiktu, kur programm\u0101 Excel tiks par\u0101d\u012bti j\u016bsu dati.<\/p>\n<p>Power Query jauda ir t\u0101 sp\u0113ja uztur\u0113t dinamiskas saites ar saviem datu avotiem. Piem\u0113ram, ja main\u0101s s\u0101kotn\u0113j\u0101 datu kopa, vienk\u0101r\u0161i ar peles labo pogu noklik\u0161\u0137iniet uz jaun\u0101s tabulas un atlasiet \u201cAtsvaidzin\u0101t\u201d, lai to nevainojami atjaunin\u0101tu.<\/p>\n<figure class=\"wp-block-image\"><img alt=\"Notiek datu atsvaidzin\u0101\u0161ana Power Query tabul\u0101\" class=\"wp-image\" decoding=\"async\" height=\"390\" loading=\"lazy\" src=\"https:\/\/cdn.howtogeek.blog\/wp-content\/uploads\/2025\/01\/the-refresh-option-in-excel-for-updating-a-table-created-through-a-power-query.webp\" title=\"Notiek datu atsvaidzin\u0101\u0161ana Power Query tabul\u0101\" width=\"750\"\/><\/figure>\n<p>\u0160\u012b funkcionalit\u0101te padara Power Query par sp\u0113\u013cu main\u012bt\u0101ju dinamiskai datu anal\u012bzei \u2014 saglab\u0101jot datu kopas aktu\u0101las bez manu\u0101las kop\u0113\u0161anas un iel\u012bm\u0113\u0161anas.<\/p>\n<p>Vai esat gatavs pacelt savas datu p\u0101rvald\u012bbas prasmes n\u0101kamaj\u0101 l\u012bmen\u012b? Izp\u0113tiet Power Query v\u0113l vair\u0101k, str\u0101d\u0101jot ar tabul\u0101m no t\u012bmek\u013ca un izbaudot t\u0101s daudzpus\u012bg\u0101s import\u0113\u0161anas iesp\u0113jas. J\u016bs atrad\u012bsiet \u0161o r\u012bku, kas \u0101tri paaugstin\u0101s j\u016bsu produktivit\u0101ti un precizit\u0101ti!<\/p>\n<h3>Papildu ieskati<\/h3>\n<h4><strong>1.\u00a0K\u0101da veida datu avotus varu izmantot programm\u0101 Power Query?<\/strong><\/h4>\n<p>Power Query atbalsta da\u017e\u0101dus datu avotus, tostarp Excel failus, CSV failus, t\u012bmek\u013ca lapas, datu b\u0101zes un daudz ko citu. Tas padara to \u013coti daudzpus\u012bgu datu import\u0113\u0161anas un p\u0101rveido\u0161anas uzdevumiem.<\/p>\n<h4><strong>2.\u00a0Vai varu autom\u0101tiski atsvaidzin\u0101t datus, kas import\u0113ti, izmantojot Power Query?<\/strong><\/h4>\n<p>J\u0101, varat iestat\u012bt autom\u0101tisku atsvaidzin\u0101\u0161anu sav\u0101m Power Query datu kop\u0101m. Programma Excel \u013cauj konfigur\u0113t atsvaidzin\u0101\u0161anas interv\u0101lus, \u013caujot j\u016bsu datiem palikt atjaunin\u0101tiem bez manu\u0101las iejauk\u0161an\u0101s.<\/p>\n<h4><strong>3.\u00a0Vai ir ierobe\u017eots datu apjoms, ko varu import\u0113t, izmantojot Power Query?<\/strong><\/h4>\n<p>Lai gan Power Query var apstr\u0101d\u0101t lielas datu kopas, veiktsp\u0113ja var pasliktin\u0101ties ar p\u0101r\u0101k liel\u0101m kop\u0101m, \u012bpa\u0161i, ja t\u0101s p\u0101rsniedz Excel rindu ierobe\u017eojumu\u00a0\u2014 1\u00a0048\u00a0576. Pirms import\u0113\u0161anas ir ieteicams filtr\u0113t vai apkopot datus, lai uzlabotu apstr\u0101des veiktsp\u0113ju.<\/p>\n<p><a class=\"xiaomi\" href=\"https:\/\/www.howtogeek.com\/how-to-clean-up-and-import-data-using-power-query-in-excel\/\" rel=\"noopener noreferrer nofollow\" target=\"_blank\">Avots un att\u0113li<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"<p>\u0100tr\u0101s saites Kop\u0161 debijas programm\u0101 Excel Power Query ir main\u012bjis veidu, k\u0101 profesion\u0101\u013ci apstr\u0101d\u0101 datu p\u0101rvald\u012bbu. S\u0101kotn\u0113ji 2010.\u00a0gad\u0101 ieviests k\u0101 pievienojumprogramma, Power Query kop\u0161 2016.\u00a0gada ir k\u013cuvis par neaizst\u0101jamu ieb\u016bv\u0113to l\u012bdzekli programm\u0101 Excel. Vai t\u0101 ir galven\u0101 funkcija? Datu t\u012br\u012b\u0161anas, import\u0113\u0161anas un p\u0101rk\u0101rto\u0161anas procesa racionaliz\u0113\u0161ana, lai ietaup\u012btu ne tikai laiku, bet ar\u012b palielin\u0101tu neapmierin\u0101t\u012bbu. Koncentr\u0113joties [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[130,286,9],"class_list":["post-6759","post","type-post","status-publish","format-standard","hentry","category-how-to","tag-microsoft","tag-microsoft-excel","tag-windows"],"acf":[],"_links":{"self":[{"href":"https:\/\/howtogeek.blog\/lv\/wp-json\/wp\/v2\/posts\/6759","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/howtogeek.blog\/lv\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/howtogeek.blog\/lv\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/howtogeek.blog\/lv\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/howtogeek.blog\/lv\/wp-json\/wp\/v2\/comments?post=6759"}],"version-history":[{"count":1,"href":"https:\/\/howtogeek.blog\/lv\/wp-json\/wp\/v2\/posts\/6759\/revisions"}],"predecessor-version":[{"id":6760,"href":"https:\/\/howtogeek.blog\/lv\/wp-json\/wp\/v2\/posts\/6759\/revisions\/6760"}],"wp:attachment":[{"href":"https:\/\/howtogeek.blog\/lv\/wp-json\/wp\/v2\/media?parent=6759"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/howtogeek.blog\/lv\/wp-json\/wp\/v2\/categories?post=6759"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/howtogeek.blog\/lv\/wp-json\/wp\/v2\/tags?post=6759"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}